SUMIF

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 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.


Excel Functions and Formulas Sitemap


Go to the next lesson:
Lesson 16: ISERROR

 

 

 

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