98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum 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?
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)
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.
Negative entries
How does one make Excel sum up only positive values and automatically ignore negative entries in a simple summation?
Number calculation
How do i calculate Various numbers in different fields
Related articles
How Reorganising Your Data Can Take Your Business to the Next Level
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.
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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |