## 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

- 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.
- In cell E1 type the heading Daily Sales and press enter.
- In cell E2 type the following formula:

=SUMIF($A$2:$A$15,D2,$B$2:$B$15)

- Press the Enter key and you should see the number 550 in cell E2.
- 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.
- 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.
- 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.

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")