SUMIFS

Excel functions                              Microsoft Excel Examples

 

 

Excel tips on functions and formulas

Excel Functions and Formulas

Excel examples

Excel and VBA Expert

Excel fonctions et formules

Special

Excel Functions

2003-2010

 

sumproduct

index-match

subtotal

iserror

countif

sumif

hlookup

lookup

vlookup

 

 

Special

Excel Functions

2007-2010

 

averageif

averageifs

countifs

sumifs

iferror

 

 

 

 

 

 

 

Excel Functions and Formulas

 

Excel Macros and VBA

Lesson 24: SUMIFS  Function (Excel 2007)

 

A

B

C

D

1

Month

State

Name

Amount

2

5/1/2009

MI

John

2

3

6/1/2009

NY

Peter

6

4

6/1/2009

PA

John

8

5

5/1/2009

NH

Peter

3

6

6/1/2009

MI

John

7

7

5/1/2009

FL

Peter

6

The general format for the SUMIFS formula is as follow: =SUMIFS(range to sum, range of criteria 1, criteria 1, range of criteria 2, criteria 2) and you can have as many criteria as you need.

It is a little different than the SUMIF function that could only handle a single criteria and that it replaces.

In this first example the criteria applies :
=SUMIFS(D2:D7,D2:D7,">5")
In plain English it says: sum the values of the cells D2 to D7 if they are greater than 5. The result should be 27.

You can have many critrria like in the example below:
=SUMIFS(D2:D7,D2:D7,">5",C2:C7,"John") which results in 8 because it is summing the values in D2 to D7 greater than 5 if the name in C2 to C7 is John.
In the above formula you can replace John by its address:
=SUMIFS(D2:D7,D2:D7,">5",C2:C7,C2)

SUMPRODUCT can do More

If you want to sum the amounts based on its value, on the name AND on the month, you can not use SUMIF or SUMIFS have to use SUMPRODUCT. Here is an example:
=SUMPRODUCT((MONTH(A2:A7)=6)*(C2:C7="John")*(D2:D7>5)*(D2:D7))
The formula above sums all value grater than 5 in D2 to D7 if the value in C5 to C7 is John and if the month of the date in A2 to A7 is 6 (June).
You could not do the above with SUMIFS

WARNING: You can only use SUMIFS if you are using Excel 2007 and if everyone who will receive your workbook are also using Excel 2007. You can do the same thing and even more in a version of Excel that is earlier than 2007. You will use SUMPRODUCT the most powerful function in Excel.


Excel Functions and Formulas Sitemap


Go to the next lesson:
Lesson 25: IFERROR (Excel 2007)

 

 

 

Excel tips on functions and formulas

Excel Functions and Formulas

Excel examples

Excel and VBA Expert

Excel fonctions et formules

SUMPRODUCT    INDEX/MATCH    SUBTOTAL    ISERROR    COUNTIF    SUMIF    HLOOKUP    LOOKUP    VLOOKUP
AVERAGEIF    AVERAGEIFS    COUNTIFS    SUMIFS    IFERROR