99.4% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
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 function?
Is is possible to relate a sum formula for cell format (i.e. sum all cells in x column where colour is red)
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.
How does one make Excel sum up only positive values and automatically ignore negative entries in a simple summation?
How do i calculate Various numbers in different fields
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).
Any suggestions, questions or comments? Please post in the Improve the forum thread.