Lesson 15: SUMIF Function in Excel
|
|
A
|
B
|
C
|
D
|
|
1
|
Date
|
State
|
Name
|
Amount
|
|
2
|
1/5/2009
|
MI
|
John
|
2
|
|
3
|
1/6/2009
|
NY
|
Peter
|
6
|
|
4
|
1/7/2009
|
PA
|
John
|
8
|
|
5
|
1/5/2009
|
NH
|
Peter
|
3
|
|
6
|
1/6/2009
|
MI
|
John
|
4
|
|
7
|
1/7/2009
|
FL
|
Peter
|
6
|
The general format for the SUMIF formula is as follows: =SUMIF(range where criteria applies, criteria, sum what).
In this first example the criteria applies to the range that is summed:
=SUMIF(D2:D7,">5",D2:D7)
In plain English it says sum the values of the cells D2 to D7 if they are greater than 5. The result should be 20.
The criteria can also be in another column. Applied to the table above, the result of the following formula should be 14:
=SUMIF(C2:C7,"John" ,D2:D7)
or
=SUMIF(C2:C7,C2 ,D2:D7)
In plain English it says: sum the values of the cells D2 to D7 if the value in cells C2 to C7 is John or equivalent to the value in cell C2.
If you want to sum the amounts based on month, State AND name, discover the very simple SUMPRODUCT function with which you can use 2, 3 or more criteria.
SUMIF Function in Excel
Links to the top of the page
Excel Functions and Formulas Sitemap
Downloadable Tutorials, Examples and Templates
|
