SUMIF is considered one of the important Excel functions to learn. This tutorial explains how to use SUMIF function in Excel, and it's intended to be a comprehensive guide with many real-life SUMIF formula examples.
SUMIF adds more functionalities to the basic SUM formula by introducing selection criteria. The SUMIF function returns the sum of all the cells from a range when the supplied criteria is met. This means that you can take a range and only add up certain cells while completely ignoring the rest.
The SUMIF syntax is really simple and only uses three arguments:
=SUMIF(range, criteria, [sum_range])
The SUMIF function supports logical operators (>, <, <>, =) and wildcards (*, ?) for partial matching.
If you want to apply multiple criteria, I recommend using the SUMIFS function.
Now let's look at a few SUMIF examples. Let's calculate the total sales of pizza.
To write the SUMIF formula, follow these steps:
The formula I ended up with is:
=SUMIF(C3:C12, "Pizza", D3:D12)
Note: If you use text criteria, you need to wrap them inside double quotation marks. Also, logical operators need to be enclosed in double quotation marks (e.g. "<15").
Alternatively, the same result can be achieved by including the criteria using a cell reference:
=SUMIF(C3:C12, G2, D3:D12)
These simple formulas use the SUMIF function to return only the sum of pizza sales while ignoring the rest of the data.
A sum if equal to formula is the easiest and works with numbers and text. The formula doesn't require the equal sign, as this is how the SUMIF Excel function is built.
Let's say we want to calculate the total orders placed by customers that dined at table 1. We can write a simple SUMIF formula that looks like this:
=SUMIF(E3:E12, 1, D3:D12)
If you prefer, you can also write this formula as:
=SUMIF(E3:E12, "=1", D3:D12)
The results are the same in both cases.
You can also replace the hardcoded criteria with a cell reference to make your formulas dynamic. In this case, the SUMIF formulas would look like this:
=SUMIF(E3:E12, H2, D3:D12)
The initial example from this article showed you how to sum cells based on text input. The only difference is that you need to enclose the string between double quotation marks like this:
=SUMIF(C3:C12, "Pizza", D3:D12)
Note: You don't need the double quotation marks when using cell references as the criteria argument.
When writing a SUMIF not equal to a criteria formula, you need to use the "not equal to" logical operator ("<>"). If you hardcode the value for the criteria argument, wrap it around double quotation marks (e.g., "<>Pizza" or "<>3").
Now let's look at a simple formula that doesn't use a cell reference for the criteria argument. We will calculate the total sales for orders that don't include Pizza. The SUMIF formula is:
=SUMIF(C3:C12, "<>Pizza", D3:D12)
To use cell references, you need to concatenate the criterion with the "not equal to" logical operator like this:
=SUMIF(C3:C12, "<>"&H2, D3:D12)
Note: only the logical operator is enclosed between double quotation marks. The ampersand symbol (&) is used to concatenate the criterion with the logical operator.
If you need to sum up numbers higher than or less than a specific value, you must use logical operators. The list below contains the logical operators (a.k.a comparison operators) needed to write a sum if greater than or less than formula:
Let's assume we want to sum up the value of all the sales from customers who ordered more than 3 products. The formula can be written as:
=SUMIF(C3:C12, ">3", D3:D12)
=SUMIF(C3:C12, ">"&G2, D3:D12)
Note: If you are using a cell reference (like G2), you need to concatenate the logical operator with the cell using the ampersand symbol (&).
Alternatively, if you want to calculate the sum of all sales from customers who ordered 3 products or less, you can change the SUMIF condition while using the same range argument:
=SUMIF(C3:C12, "<=3", D3:D12)
=SUMIF(C3:C12, "<="&G2, D3:D12)
My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.
If you'd like to get in touch, you can contact me on LinkedIn.
Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.