How does the sumif statement work?

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Topics » How does the sumif statement work?

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


Related articles

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


Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Server loaded in 0.07 secs.

✓ w3speedster