How does the sumif statement work?

In Excel the SUM function is very commonly used, but there are times when you need to sum only the data that is subject to a criteria setting.

The SUM function cannot cope with this, however, Excel provides the SUMIF statement, which can. The SUMIF statement works by using the following syntax:

=SUMIF(Range,Criteria,[Sum Range]) Items in square brackets are optional.

If I wanted to find the sum of all commissions for sales that were less than £200,000 in the table below, I would use a SUMIF function =SUMIF(B4:B13,"<200000",C4:C13):

Monthly Sales Transactions

		

Sale Date:	Sale Amount:        Commission:

03-Jan	     £324,256.00 	    £4,053.20 

04-Jan	     £185,629.00 	    £2,320.36 

05-Jan	     £150,000.00 	    £1,875.00 

06-Jan	     £324,526.00 	    £4,056.58 

07-Jan	     £125,847.00 	    £1,573.09 

08-Jan	     £621,478.00 	    £7,768.48 

09-Jan	     £125,639.00 	    £1,570.49 

10-Jan	     £172,365.00 	    £2,154.56 

11-Jan	     £298,325.00 	    £3,729.06 

12-Jan	     £401,523.00 	    £5,019.04

Commission on Sales < £200,000: £9,493.50

The SUMIF formula is written into cell C15 and produces a result of £9,493.50.

Try it out for yourself!

Related forum posts:

What is the sumif functions?

What is the sumif function?
Read forum post

Formulas based on formats

Is is possible to relate a sum formula for cell format (i.e. sum all cells in x column where colour is red)
Read forum post

Excel 2007 - Addition of formatted cells

Hi, after i have formatted cells, how do then add up only some of the proceeds... i.e. if i have inserted a formula to turn them yellow... how do i only add the yellow cells? thank you.
Read forum post

Negative entries

How does one make Excel sum up only positive values and automatically ignore negative entries in a simple summation?
Read forum post

Number calculation

How do i calculate Various numbers in different fields
Read forum post

How Reorganising Your Data Can Take Your Business to the Next Level

Read article

Want To Get To Grips With The Different SUM Functions In Excel?

Once you've worked with Excel for a bit, you'll no doubt have come across different functions all containing SUM such as SUM, SUMIF, SUMIFs and DSUM. This article describes how these different functions are used to help you carry out various data analysis tasks which all involve summing. These functions differ in how you can use criteria with the SUM.
Read article

Training courses