Sumif Function in Excel | Complete Guide
In this tutorial we’ll learn what is sumif function in excel and how to use sumif function in excel with text values, date values and numerical values.
Sumif in excel is used when you want to sum numerical values based on a condition. For example, if you want to find the total sales of a specific product only and not all products.
Once you know how to use sumif function in excel, you’ll be able to master SUMIFS, COUNTIF, AVERAGEIF easily, as the logical structure is same as SUMIF.
Now let’s get started.
Table of Contents
Excel SUMIF Formula
Let’s look at an example to understand how the formula works. Here’s the sample data we’ll be using.
We have to calculate the total sales of Mobile Phones.
- Go to the cell where we want the total value and type =SUMIF(B2:B7, “Mobile Phones”,C2:C7) and press enter
- Now you can see the total sales of Mobile Phones.
In case you want to calculate the sales for Speakers just change “Mobile Phones” to “Speakers” in the formula.
Now let’s understand the syntax of the sumif formula:
=SUMIF(range, criteria, sum_range)
- Range: The range of cells which you want to get evaluated. Example: B2:B7 which contains the product sold by all the employees.
- Criteria: This is the condition which needs to be met. Excel will look for this condition and when the criteria is right, it’ll sum up the respective values. Example: “Mobile Phones” tells excel to look for the text Mobile Phones and then sum up the respective values.
- Sum Range: This is the range of cells which will be summed up. After telling excel the range of cells to be checked and criteria/condition to look for, this tells excel which numerical values are to be summed. Example: C2:C7 tells excel to sum up the numerical values in this range where the criteria is met.
SUMIF Function Excel – Greater than, Less than and Equal to
SUMIF is not limited to only summing up where the criteria is a text value but you can also sum values even if your criteria is actually a numeric value.
For example, you want to sum up the quantity of those products where the minimum Qty is 100 or where the maximum Qty is 300 or even where the Qty is exactly equal to 200. Let’s see how.
- Go to the cell and type =SUMIF(B2:B6, “>100”) and hit enter
- This will sum up the Quantity of the products where the Quantity was greater than 100
Here’s a short table explaining all different variations.
| Particulars | Formula | Details |
|---|---|---|
SUMIF Greater than | =SUMIF(B2:B6, “>100”) | This will sum up Qty of products where Qty is greater than 100 |
SUMIF Greater than or equal to | =SUMIF(B2:B6, “>=100”) | This will sum up Qty of products where Qty is greater than or equal to 100 |
SUMIF Less than | =SUMIF(B2:B6, 100) | This will sum up Qty of products where Qty is equal to 100 |
SUMIF Less than or equal to | =SUMIF(B2:B6, “<=100”) | This will sum up Qty of products where Qty is less than or equal to 100 |
SUMIF Equal to | =SUMIF(B2:B6, “>100”) | This will sum up Qty of products where Qty is greater than 100 |
Excel SUMIF Function for Blank Cells
Let’s see how you can use SUMIF to add up values where cells are blank.
- As you can see below there are few empty cells. To sum up values corresponding to blank cells, type the formula =SUMIF(A2:A6,”=”,B2:B6) and hit enter
- Excel has summed up the values corresponding to the blank cells.
Excel SUMIF Formula – With & without text
Now let’s see how to use sumif function in excel for exact and partial matches. See the data below.
Let’s calculate the total quantity of all kinds of shirts from the data.
- Use the formula =SUMIF(B2:B7, “*Shirt*”, C2:C7) and hit enter
- Now you can see excel has summed up the quantity of all types of shirts excluding any other type of SKU
*Shirt* tells excel to look for the text Shirt alone or in combination with any word. That’s why cells “Red Shirt”, “White Shirt” and “Blue Shirt” all got summed. This was partial match within sumif function.
Here’s a short table explaining all the different variations you can use.
| Particulars | Formula | Details |
|---|---|---|
SUMIF Equal to
Exact Match | =SUMIF(B2:B7, “Red Shirt”, C2:C7) | This will sum up the quantity of only red shirts. Any other type of products or words will be ignored |
SUMIF Equal to
Partial Match | =SUMIF(B2:B7, “*Shirt*”, C2:C7) | This will sum up the quantity of all products having the word “Shirt” alone or in any combination. |
SUMIF Not equal to
Exact Match | =SUMIF(B2:B7, “Shirt”, C2:C7) | This will sum up the quantity of all products except the ones where the text is “Shirt”. Products such as Red Shirt or White Shirt will be summed. |
SUMIF Not equal to
Partial Match | =SUMIF(B2:B7, “*Shirt*”, C2:C7) | This will sum up the quantity of all products except the ones having the word “Shirt” alone or in any combination. |
Excel SUMIF Function for Dates
You can also use SUMIF formula to add up values corresponding to dates. Let’s see how.
- Let’s sum up the sales transactions where the date is 10 Feb 2022. Type the formula =SUMIF(A2:A7,”10-Feb-2022”,C2:C7) and hit enter
- The only values summed up are the ones specific to 10 Feb 2022.
Here are some other variations you can use.
| Particulars | Formula | Details |
|---|---|---|
SUMIF Greater than or equal to | =SUMIF(A2:A7,”>=10-Feb-2022”,C2:C7) | This will sum up the values corresponding to date equal to or greater/later than 10 Feb 2022 |
SUMIF Less than or equal to | =SUMIF(A2:A7,”<=10-Feb-2022”,C2:C7) | This will sum up the values corresponding to date equal to or lesser/earlier than 10 Feb 2022 |
SUMIF Formula Not Working
Here are some of the reasons why your SUMIF formula might not be working.
- Syntax: In case the formula isn’t working, make sure to double check the syntax.
The correct syntax is
=SUMIF(range, criteria, sum_range) - Range & Sum Range: Although if the range & sum range aren’t of equal sizes it’s completely fine. Still it’s suggested to make sure the range & sum range sizes are equal in the formula.
Conclusion
This was all about excel sumif function.
I hope this tutorial covered enough sumif example in excel for you to understand how excel formula for sumif works.
To help you further here’s a tutorial on how SUMIFS function in excel works. If you have any questions or tips, I’d love to hear them in the comment section below.
Advanced Excel Course
Learn Vlookup, Pivot tables, What IF, Macro Automation, Power Query and much more.
- 10+Hrs of Learning Content
- 50+ Practical Exercises
- Certification
- Doubt Resolution
