The SUM function in Excel allows you to add up the values in a range of cells. However, sometimes you only want to add up the cells that meet certain criteria. That's where the SUMIF function comes in handy, along with the more capable SUMIFS function.
There are two common scenarios for using SUMIF:
You want to add up all the cells in a range that meet a certain criteria, e.g. all cells in a range (e.g. Sales) that contain a value of $500 or higher.
You want to add up all the cells in a range where the cells in another range meet a certain criteria, e.g. add up all cells in a column (e.g. Sales) where the cells in another column (e.g. Quantity Sold) is 5 or more.
SUMIF function syntax
The SUMIF function has the following syntax:
range is the range of cells you want to add up. It is required for the function to work.
criteria is the criteria which must be met for a cell to be included in the total. It is also required.
sum_range is the range of cells that will be added up. It is optional; if you leave it out, Excel will check the criteria against the sum_range. In the two examples above, the first example doesn't need you to provide a sum_range, while the second example does.
One of the tricky things when constructing a SUMIF function is how to present the criteria. Here are some examples to help you:
To add up all values that equal 500: enter 500 as the criteria. Excel knows you want to match cells with a value of 500.
To add up all values that are greater than 500: enter ">500" as the criteria. Notice the use of quotation marks around the criteria. You'll get an error if you leave those out.
To add up all values that are greater than or equal to 500: enter ">=500".
SUMIF in action - adding up all sales that are greater than a certain number.
The following example is a simple way to demonstrate SUMIF in action where we won't include the criteria_range argument. We'll use the first scenario given at the beginning of this lesson.
Imagine you have a table showing sales for the week.
You want to find the total of sales for only those days where sales were greater than $500.
You enter the SUMIF formula in to a cell at the bottom of the column of sales figures (along with the SUM formula to give you the overall total)
Row 14 contains the SUMIF function, and the outcome of the SUMIF function in C14.
SUMIF in action - adding up all sales where the sales quantity is greater than a certain number.
Let's extend the example in the table above to only add up sales where the quantity sold was 5 or more. This is the second scenario described at the start of the lesson.
After adding a Quantity column, the table above now looks like this:
The formula in C4 has been changed to reflect the new criteria, and has also included the sum_range. Now, the SUMIF function checks the quantities in column B to see if they match the criteria supplied, and adds the sales value in column C if they do.
You can also use a cell reference as your criteria (i.e. the value in another cell) rather than a number as shown in the examples above. Let's assume that cell D5 contains the value we want to use for our criteria. Here are a couple of examples of formulas using D5:
=SUMIF(B18:B23,D5,C18:C23) - this will use the value in D5 as the criteria, and sum any values that equal the value in D5
=SUMIF(B18:B23,>D5,C18:C23) - this will NOT work, and Excel will not accept this formula if you type it in.
=SUMIF(B18:B23,">"&D5,C18:C23) - this will use >D5 as the criteria, i.e. any values greater than the value in D5.
=SUMIF(B18:B23,">="&D5,C18:C23) - this will use >=D5 as the criteria, i.e. any values greater than or equal to the value in D5.
In the third and fourth examples, we have to play a bit of a trick on Excel to get it to recognise our criteria (remember that the second example doesn't work, which is where most people get stuck).
I'd love to hear your feedback about this lesson. Did it help? Is it accurate? Do you have a particular problem you want to solve and you can't quite get the SUMIF function to work for you? Why not add a comment or ask a question about your specific scenario below.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note - if you post a link in your comment, it will automatically be deleted.