- In cell F1, type “No. Sales” and format if necessary.
- In cell F2, type =SUMIF( and press Ctrl+A.
The Function Arguments dialog box appears.
- In the Range field, enter the items you’re adding together (B2:B22), and then press F4.
- In the Criteria field, enter the criteria you’re looking for in that range (E2).
You don’t press the F4 key here, because you want to copy it down the column.
- In the Sum_range field, enter the numbers you want to sum together (C2:C22), and then press F4.
Check out what this should look like.
- Click OK.
The resulting formula will be =SUMIF($B$2:$B$22,E2,$C$2:$C$22) with the calculated value of $99,310.
- Copy the formula down the column.
- Click cell G6, use the shortcut Alt+=, and press Enter to add the sum total.
The calculated value is $384,805.
- Format as necessary.
- In cell G7, enter the formula =SUM(C2:C22)-G6 to make sure the totals are the same.
- Format the zero to a dash by clicking the comma button in the Number section of the Home tab.
- Check your numbers against this example.