Excel boasts 100+ statistical and mathematical functions. It is one of the top spreadsheet applications for complex data organization and analysis. Users can leverage functions like the SUMIF to find the sum of given cells.
The function allows them to add numbers based on criteria defined by them.
This is a quick article in which we will dive deep into the syntax of the SUMIF function and learn to find the sum of a range of cells that satisfies a given condition. Make sure you read the article until the end because we are about to discuss various examples with step-by-step instructions.
You can use the SUMIF function on text strings, numbers, and even dates. Click on the following links to jump to the particular section of your interest:
- The SUMIF function using the Text String as a criteria
- The SUMIF function using the Numbers as a criteria
- The SUMIF function using the Dates as a criteria
Download the Example Excel Sheet
If you don’t have your own workbook ready to practice the SUMIF function, please consider downloading the following Excel spreadsheet.
It includes several sheets with the required datasets to demonstrate various examples.
Practice is crucial to mastering the SUMIF function, so make sure you follow me as we explore further sections of this sector.
Explained: The SUMIF Function in Excel
The SUMIF function in Excel helps users calculate the sum of the range based on one or more criteria. The criteria are defined using dates, numbers, and text.
It also supports logical operators like “<” (less than), “>”, and “=” (equal to). Anyone from beginners to advanced users can use the function.
The general syntax for the SUMIF function is as follows,
=SUMIF(range,criteria,[sum_range])Each argument of the function needs to be replaced with the proper information.
- “range” – It represents the range of cells you wish to sum. The range defined here will be tested against the criteria specified by the users. Then, the values from the range that meet the criteria are added to find the sum.
- “criteria” – Here, you can define the criteria, also known as the condition. It will be tested against the cell range to determine eligible cells for finding the desired output.
- “sum_range” – It is an optional argument. If you keep it blank, the function will add the numbers from the range defined in the place of the first argument. Here, you should specify the secondary range of cells you wish to sum.
As seen above, the syntax of this SUMIF function is pretty straightforward. You can use the function to find the sales generated by individual employees, people attending an event during a specific time, monthly revenue by product categories, and much more.
Excel SUMIF Function Examples
Now that you have learned the syntax of the function, let us use the knowledge to add cell values based on a text string, number, and date criteria. The SUMIF function is relatively easy to use, but the correctness of the output truly depends upon the complexity of the scenario you deal with.
Let us discuss a few scenarios that would strengthen your knowledge of the SUMIF function and prepare you for future complex data analysis.
How to use the SUMIF function to add up numbers with the Text String as a criteria
In this example, we use the text string as the criteria to test the given cell range. Most of the time, Excel users need to define the criteria using the text string when applying the SUMIF function to the given dataset.
EXAMPLE #1 – Find the sum of the cells that contain a specific text string
Consider the following dataset, which includes employee details, including their name, department, and annual salary.
Our task is to find the total salary that needs to be paid to the employees from the IT department.
Let’s begin,
- Let us add the following section to the spreadsheet
- Click on the cell “F2”
- Type “=SUMIF”
- Select the first option from the popup or press “Tab” key
- Now, as we are interested in finding the amount spent on the salary of the IT department, the “range” argument will be replaced with the “B2:B20“
- Press “,” to move to the next argument
- In place of the “criteria” argument, type “IT“
(Make sure to put the keyword “IT” within double quotation marks.) - Press the “,” key on your keyboard
- Provide the cell range “C2:C20” for the “sum_range” argument
- Complete the formula with a closing parenthesis “)“
- Press “Enter“
Our final formula after following the above steps is as follows,
=SUMIF(B2:B20,"IT",C2:C20)Excel will instantly display the total amount spent on the IT department employees, as shown in the GIF above.
EXAMPLE #2 – Find the sum of the cells that do not contain a specific text string
Now, let us reverse the condition. Here, we will find the sum of the cells that do not contain the given text string.
The dataset for this example remains the same,
Our task is to find the amount spent as salary on all departments except the IT department.
Note that for this purpose, we need to leverage Comparison Operators in Excel. There are a total of six of them, as follows,
- “=” – Equal To
- “<” – Less Than
- “<=” – Less Than or Equal To
- “>” – Greater Than
- “>=” – Greater Than or Equal To
- “<>” – Not Equal To
As you have already guessed, we need to use the Not Equal To (“<>”) operator to exclude the IT department from our calculation.
Here are the steps,
- Select the desired cell
- Type “=SUMIF”
- Select the first option from the popup or press “Tab“
- Provide the cell reference as “B2:B20” for the “range” argument
- Press “,” to move to the next argument
- Now, using the Not Equal To operator, let us type “<>IT” and make sure to use the double quotation marks
- Press “,” key
- Provide the cell reference as “C2:C20” for the “sum_range” argument
- Close the bracket using “)“
- Press “Enter“
Our final formula after following the above steps is as follows,
=SUMIF(B2:B20,"<>IT",C2:C20)Note that most of the Comparison Operators are particularly useful in the case of the Numbers. The Equal To and Not Equal To operators are used for both text strings and numbers.
EXAMPLE #3 – Find the sum of the cells that contain a portion of the text string
Sometimes, you may need to define the criteria for finding the cells that contain a portion of the given text string.
Consider the following dataset, which contains fruit names and their prices. Our task is to find the total cost of the Apples.
Note that there are three different types of apples: Normal Apple, Red Apple, and Green Apple.
We need to use wildcard characters in Excel to create criteria to identify cells holding the portion of the text string.
- “?” – The question mark wildcard lets users find the cells that contain a single character
- “*” – The asterisk wildcard lets users find the cells that contain a sequence of characters
As the keyword “Apple” is made up of a sequence of characters, we need to use the Asterisk “*” wildcard.
Here are the steps,
- Select the desired cell
- Type “=SUMIF”
- Select the first option from the popup or press “Tab” key
- Provide the cell reference “A2:A10” for the “range” argument
- Press “,” to move to the next argument
- Now, using the Asterisk wildcard character type and type the keyword like “*Apple”
(Make sure to enclose it in double quotation marks) - Press “,” on your keyboard
- Provide the cell reference “B2:B10” for the “sum_range” argument
- Close the bracket using “)“
- Press the “Enter” key
Our final formula would look like,
=SUMIF(A2:A10,"*Apple",B2:B10)Note: If you wish to define the criteria using the cell reference, then make sure to put the Asterisk wildcard character in double quotation marks and use the “&” symbol to join the cell reference value.
For example, we need to use the cell reference “D2”, which holds the keyword “Apple.”
Our formula will be as follows,
=SUMIF(A2:A10,"*"&D2,B2:B10)How to use the SUMIF function to add up cells with the Numbers as criteria
As mentioned at the beginning of the article, we will have to use the comparison operators in Excel to use the SUMIF function to add cells with numbers as criteria.
There are a total of six Comparison operators in Excel:
- “=” – Equal To
- “<” – Less Than
- “<=” – Less Than or Equal To
- “>” – Greater Than
- “>=” – Greater Than or Equal To
- “<>” – Not Equal To
They are self-explanatory from their name itself. However, you need to be careful while inserting them within the formula and make sure to use double quotation marks.
Otherwise, the function will return an error.
EXAMPLE #4 – Find the sum of the cells that are equal to the given number
Let’s start with a simple scenario that uses the Equal To comparison operator.
Here’s the example dataset, which has product names in the first column, items sold in the second column, and unit prices in the third column.
Task: To find the total price of the products whose 15 units have been sold over time.
Note that for the sake of this article, we are simply calculating the total price of the product and not the revenue generated by selling each product. The method is the same but requires a bit of adjustment.
Let’s begin,
- Select the desired cell
- Type “=SUMIF”
- Click on the first option from the popup or press the “Tab” key
- Provide the cell reference “B2:B10“
- Press “,” to move to the next argument
- Now, we will use the Equal To comparison operator and type “=15”
(Make sure to use double quotation marks to avoid errors at the end.) - Press “,“
- Provide the cell reference “C2:C10” for the “sum_range” argument
- Complete the bracket using “)“
- Press the “Enter” key
Our final formula will be as follows,
=SUMIF(B2:B10,"=15",C2:C10)As 15 units of the Orange, Watermelon, and Green Apple are sold, and their prices are $61, $56, and $95, the total sum of their prices is $212.
EXAMPLE #5 – Find the sum of the cells that are greater than the given number
For this type of example, we need to use the “Greater than (>)” comparison operator.
We will use the dataset below, which contains employee details, including their full name, the total number of years they have served the given company, and their annual salary.
Task: To find the salary of the employees who have been working for more than two years for the given company.
Here are the steps,
- Select the desired cell
- Type “=SUMIF”
- Select the first option from the popup or press “Tab” key
- Provide the cell reference “B2:B10” for the “range” argument
- Press “,” to move to the next argument
- Now, using the Greater Than comparison operator type “>2“
(Make sure to use double quotation marks; otherwise, the formula will return an error at the end.) - Press “,” on your keyboard
- Provide the cell reference “C2:C10” for the “sum_range” argument
- Complete the bracket using “)“
- Press the “Enter” key
After following the above steps, the final formula would look like,
=SUMIF(B2:B10,">2",C2:C10)Also, in the case of the cell reference, make sure to use the double quotation marks to enclose all three parameters,
- Comparison Operator
- The “&” symbol
- Cell Reference
For example, the formula with the cell reference “D1” would be as follows,
=SUMIF(B2:B10,">"&D1,C2:C10)EXAMPLE #6 – Find the sum of the cells that are greater than or equal to the given number
We will consider the dataset used in the previous section to demonstrate this scenario.
Task: To find the salary of the employees who have been working for the company for the last one year and more than that.
The steps are similar, and you only need to use the Greater Than or Equal To (>=) operator.
Let’s begin,
- Select the desired cell
- Type “=SUMIF”
- Choose the first option from the popup or press “Tab“
- Provide the cell reference “B2:B10” for the “range” argument
- Press “,” to move to the next argument
- Next, using the Greater Than or Equal To comparison operator, type “>=1“
(Make sure to use double quotation marks; otherwise, the formula won’t work at the end.) - Press “,” on your keyboard
- Replace the “sum_range” argument with the cell reference “C2:C10“
- Complete the formula with a closing parenthesis “)“
- Press “Enter“
Here’s the final formula using the steps above,
=SUMIF(B2:B10,">=1",C2:C10)How to use the SUMIF function to add up numbers with the Dates as a criteria
To find the sum of the cells that satisfy the given date criteria, you need to use the Comparison Operators discussed in the previous section.
EXAMPLE #7 – Find the sum of the cells that are equal to a specific date
The Equal To comparison operator is your solution for this problem. Consider the following dataset, which includes sales in January.
As you can see in the above image, the table is sorted by the Sales column in descending order. Also, there are multiple entries for a few dates.
Our task is to find the total sales on January 13th, 2023, in the provided dataset.
Let’s begin,
- Select the desired cell
- Type “=SUMIF”
- Select the first option from the popup or press “Tab“
- Provide the cell reference as “A2:A20“
- Press “,” to move to the next argument
- Now, use the equal operator and replace the “criteria” argument with the “=1/13/2023“
(Make sure to enter a valid date in one of the acceptable formats for Excel to interpret as a date) - Press “,“
- Provide the cell reference “B2:B20” for the “sum_range” argument
- End the formula using the closing parenthesis “)“
- Press “Enter“
Here’s how our final formula looks,
=SUMIF(A2:A20,"=1/13/2023",B2:B20)EXAMPLE #8 – Find the sum of the cells that contain a date before a specific date
Let us use the same dataset as the one used in the previous example. Our task is to find the sales before January 15th, 2023.
We will use the Less Than or Equal To operator for this purpose.
Let’s begin,
- Select the desired cell
- Type “=SUMIF”
- Select the first option from the popup or press the “Tab” key
- Provide the cell reference “A2:A20“
- Press “,” to move to the next argument
- Next, we will use the Less Than or Equal To comparison operator to find the total sales before January 15th, 2023. Type “<=1/15/2023”
(Make sure to use double quotation marks and enter the date in a predefined format.) - Press “,“
- Provide the cell reference “B2:B20“
- Press “Enter” to see the result
After following the above steps, your final formula should be as follows,
=SUMIF(A2:A20,"<=1/15/2023",B2:B20)Things to know before using the SUMIF function in Excel
To avoid potential errors and incorrect calculations, there are a few things that you need to make a note of before using the SUMIF function.
1. The range defined in the “range” and “sum_range” criteria should be equal
Excel will not return an error if the ranges defined in the place of the “range” and “sum_range” arguments are different.
But it may mess up your final results.
For instance, let’s assume we wish to find the sales generated by John Doe, and our formula is as follows,
=SUMIF(A2:A10,"Jon Doe",B2:B10)In the above formula, “A2:A10” is the cell range against which the criteria “John Doe” will be tested, and “B2:B20” represents the cell range that includes the values to be added.
The sales generated by John Doe is “$143,603”. Refer to the above image.
Now, let’s add a few more entries, as shown below,
Also, let us change the sum range from “B2:B10” to “B2:B20” and keep the range “A2:A10” as is in our formula.
=SUMIF(A2:A10,"John Doe",B2:B20)So, as the actual sum range has been increased, as shown in the above formula, the sum should be greater than the earlier. But look at the results using the above formula,
The result remains unaffected.
It is because we haven’t increased the range from “A2:A10” to “A2:A20”. In other words, the criteria will be tested against the cells that fall between A2 and A10. So, the results remain the same!
Now, if you change the formula and increase both the range and sum range, it should look like the below,
=SUMIF(A2:A20,"John Doe",B2:B20)The results will change from “$143,603” to “$258,458”, as shown in the following image.
2. The criteria need to be put in double quotation marks
Whenever you define the criteria, never forget to put it in double quotation marks.
This rule applies to everything from text strings to dates and even while using logical operators and wildcard characters.
If you don’t use the double quotation marks, then Excel will not recognize the criteria or interpret them correctly, providing inaccurate results. Refer to the following image.
Here, Excel does not recognize “John Doe” as a text string, and the result is incorrect.
3. The “criteria” argument of the SUMIF function is not case-sensitive
The SUMIF function doesn’t differentiate between lowercase and uppercase letters.
For example, the criteria including the keywords “John Doe” and “john doe” will return the same results. In other words, the SUMIF function will only consider the characters regardless of whether they are in lowercase or uppercase while adding the numbers.
If you want to build a case-sensitive formula, you need to combine the SUMIF with the other functions in Excel.
4. Only one criterion or condition can be tested using the SUMIF function
As per the syntax of the SUMIF function, it allows users to test only one criterion against the given cell range.
Users can define a single condition and evaluate it against a single cell range. The SUMIFS function tests multiple criteria against multiple cell ranges.
Here’s the syntax for the SUMIFS function,
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)FAQs
Q. How do I find the sum of the cells that are blank?
To find the sum of the blank cells, you need to use the “Equal to (=)” operator. Let us understand this with the help of the following example.
The blank cells in column B represent the products whose zero quantities have been sold.
To add up the price of the products that didn’t make any sales over time, your formula will be as follows,
=SUMIF(B2:B10,"=",C2:C10)Here, we have used “=” (Equal To) to find the blank cells within column B, as shown below,
Q. Why does SUMIF return a “0”?
Sometimes, when you enter the formula, the SUMIF returns the output as “0”. It happens due to several reasons:
- The criteria you specify don’t match the cell range. If no cells meet the criteria, then the SUMIF function returns a “0”.
- The criteria defined are not formatted properly. Whether a number, text string, or date, the criteria should be enclosed in double quotations.
- The “sum_range” is not of the same size as the “range.” If the ranges are misaligned, SUMIF will not work as expected.
- If the data types in “range” and “criteria” are not the same, ensure both data types are compatible.
- If there are blank cells or errors in the “range” or “sum_range.” Make sure to check that both ranges contain the same data.