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.

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

1 SUMIF Function Example
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
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.

General Finance
Mergers / Acquisitions
Money Market
Real Estate
Other Personal Finance
Opinions / Essays
Excel Shortcuts
sumif function
vlookup and hlookup
Mandelbrot Set

Contact Us

Site Map