SUMIF Function
Excel's sumif function is useful for adding up values in one column based on the contents of another column. While the vlookup function can only grab one value, the sumif function adds up all the values, given a criteria.
Syntax:
SUMIF(range, criteria, [sum_range])
- range is the range (usually a column) that contain the values that will be compared to the criteria.
- criteria is a number, range, or text that contains the item to which the range will be compared. For example, if you want to add up all values when there is a number "32" in the range, then you can simply type in 32. If you want all values when there is a number greater than 32, then you must type in ">32" -- WITH quotation marks. If 32 is in cell B4, then you can type in B4 (or $B$4) for exact matches or ">" & B4. Note that you cannot type ">B4".
- sum_range is an optional parameter. You only need to use it if the range that contain the numbers you want to sum up is the same as the range you are comparing to the criteria. In most real-world cases I use this.
SUMIF Example
|
A |
B |
C |
D |
| 1 |
SUMIF Function Example |
| 2 |
|
|
|
|
| 3 |
Account |
Subaccount |
Entry |
|
| 4 |
10100 |
a |
50 |
|
| 5 |
10200 |
b |
25 |
|
| 6 |
10300 |
c |
200 |
|
| 7 |
10100 |
a |
100 |
|
| 8 |
10100 |
b |
75 |
|
| 9 |
10300 |
c |
100 |
|
| 10 |
10100 |
c |
25 |
|
| 11 |
10200 |
a |
25 |
|
| 12 |
|
|
|
|
| 13 |
Account |
Total |
|
Formula |
| 14 |
10100 |
250 |
|
=SUMIF($A$4:$A$11,$A14,$C$4:$C$11) |
| 15 |
10200 |
50 |
|
=SUMIF($A$4:$A$11,$A15,$C$4:$C$11) |
| 16 |
10300 |
300 |
|
=SUMIF($A$4:$A$11,$A16,$C$4:$C$11) |
| 17 |
10400 |
0 |
|
=SUMIF($A$4:$A$11,$A17,$C$4:$C$11) |
You can also download the sumif example Excel file.
|
|