Special

Excel Functions

2003-2010

Special

Excel Functions

2007-2010

Lesson 15: SUMIF  Function in Excel

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.

Excel Functions and Formulas Sitemap

Go to the next lesson:
Lesson 16: ISERROR