Using SUMIF to sum by category

Microsoft Excel - 97, 2000, 2002, 2003

Excel's SUMIF function allows you to sum data associated with a certain value. A simple example will make this explanation easier to follow.

Let us enter the following data in two columns on a spreadsheet:

       A           B
1     Day         Sales
2     Monday      $250
3     Tuesday     $325
4     Wednesday   $500
5     Thursday    $550
6     Friday      $200
7     Saturday    $640
8     Sunday      $675
9     Monday      $300
10    Tuesday     $400
11    Wednesday   $400
12    Thursday    $500
13    Friday      $200
14    Saturday    $700
15    Sunday      $700
  1. In cell D2 type Monday then click and drag the Fill Handle (black dot in the lower right corner of the cell) down to cell D8 and let go the mouse button to fill the range with the days of the week.
  2. In cell E1 type the heading Daily Sales and press enter.
  3. In cell E2 type the following formula:
    =SUMIF($A$2:$A$15,D2,$B$2:$B$15)
  4. Press the Enter key and you should see the number 550 in cell E2.
  5. As you can see, the SUMIF function has three parts to it. The first part is the range $A$2:$A$15, which is the Range of cells we are matching the criteria against.
  6. The second part of the function is the Criteria, what we are looking for. This is D2. So, we are going to look for the contents of D2 (Monday) in the range $A$2:$A$15. We could have typed "Monday" but this enables us to fill our formula down the range in column E.
  7. The third part of the formula is the Sum Range, once we have found matches to our criteria, the values in $B$2:$B$15 are summed. So, we sum up each number in column B that has a corresponding Monday in column A.
Because we use D2 as the criteria instead of string "Monday", you can fill this formula down to cell E8 and get the totals for each day. We will fill the formula down the range in column E just as we did to fill the days of the week in column D. Click and drag the Fill Handle (black dot in the lower right corner of the cell) down to cell E8.

Note: The third part of the SUMIF function is optional. If we are summing the values we are using to match against, we do not have to enter a sum range. For example, if you want to sum the sales when the value of sales is greater than $500, you could easily enter the following formula: =SUMIF($B$2:$B$15,">500")