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.