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.
To use SUMIF, the first two arguments are the same. 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:
=SUMIF(E2:E99,“>200”)
Summary: Use SUMIF when you need to total certain rows from a dataset on the basis of one condition.
Functions Discussed: =SUMIF()
Microsoft Excel: Total Revenue From Rows That Match A Criterion
Share to Facebook
Tidak ada komentar:
Posting Komentar