- slide 1 of 3
Strategy: Excel offers the SUMIF function, which is somewhat similar to the COUNTIF function. To count records for Ben you would use: =COUNTIF(B2: B99,“Ben”), as shown in Fig. 573.
Note: Click any figure below for a larger view of that image.
To use SUMIF, the first two arguments are the same as in the COUNTIF function. The final argument is the range to be summed. This must be the same shape as the first argument: =SUMIF(B2:B99,“Ben”,E2:E99). Instead of including “Ben” as a constant in the formula, you could enter Ben in a nearby cell and refer to the cell instead. Fig. 574 shows a table of sales by rep. The formula in E101 is copied down to E102:E104.
Additional Information: If for some reason the first and third arguments are the same range, you are allowed to drop the third argument. One example of this is if you need to sum all sales where sales are greater than 200, then you can use:
Summary: Use SUMIF when you need to total certain rows from a dataset on the basis of one condition.
Functions Discussed: =SUMIF()
For more details about the COUNTIF function, including an example, please see Count Records That Match a Criteria.
- slide 3 of 3
References and Additional Resources
If you're looking for more tips and tutorials, check out 91 Tips for Calculating With Microsoft Excel. This collection of easy-to-follow guides shows how to customize charts and graphs, different ways to make complex spreadsheets easier to update, quick and easy formatting techniques, and even how to play games like Craps in Excel. If you can't find the answer to your problem, ask in the Comments section below.
Microsoft Excel Official Site, http://office.microsoft.com/en-us/excel/
Bill Jelen, Microsoft Excel 2010 In Depth, Available from Amazon.com.