Forum home » Topics » 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
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).
Frequently Asked Questions
What does 'Resolved' mean?
Any suggestions, questions or comments? Please post in the Improve the forum thread.
TrustScore
4.9
/ 5
2394 reviews
Call for assistance
We will call you back
Formerly Best Training
© 2025 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE
2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT, United Kingdom.
