Sum if cell contains text in another cell
To sum if cells contain specific text in another cell, you can use the SUMIF function with a wildcard and concatenation.
In the example shown, cell G6 contains this formula:
This formula sums the amounts in column D when a value in column C contains the text in cell F6.
How the formula works
The SUMIF function supports wildcards. An asterisk (*) means "one or more characters", while a question mark (?) means "any one character".
These wildcards allow you to create criteria such as "begins with", "ends with", "contains 3 characters" and so on.
So, for example, you use criteria like "*hat*" to match text anywhere in a cell.
In this case, we want to match the text in F6. We can't write the criteria like "*F6*" because that will match only the literal text "F6".
Instead, we need to use the concatenation operator (&) to join a reference to F6 to asterisks (*):
When Excel evaluates this argument inside the SUMIF function, it will "see" this: "*Hoodie*".
Note that SUMIF is not case-sensitive.
Alternative with SUMIFS
You can also use the SUMIFS function. SUMIFS can handle multiple criteria, and the order of the arguments is different from SUMIF. The equivalent SUMIFS formula is:
=SUMIFS(D5:D11,C5:C11,"*"&F6&"*")
Notice that the sum range always comes first in the SUMIFS function.
Using the SUMIF Function to Add Cells if They Contain Certain Text
With the help of SUMIF Function, we can also add cells that contain specific or partial text and even if the cells contain numbers and text altogether, they can also be added.
Let’s have a look on how is it done with a slight change in the formula
Using SUMIF if cells contain specific text
Figure 1. Example 1
Formula for specific text:
This is a grocery list with a range that contains specific repeated text. In order to add the sum range based on a specific text criteria, we need to:
- Take a separate column E for the criteria and F for the total quantity.
- Write down the specific criteria in E9 and E10.
- Use SUMIF formula in cell F9 with A3:A10 as range, “Fruit” as criteria instead of E9 and C3:C10 as sum_range.
- Press Enter to get the total quantity of fruit.
- Repeat the same with vegetable criteria to get the amount of total vegetables.
Using SUMIF if cells contain partial text
Figure 2. Example 2
Formula for partial text:
In this example, we need to use SUMIF function for a partial text in the range as we want to know the total number of cars sold in various countries.
- Take column D for the brand of cars as criteria and column E for the total quantity of cars sold.
- Use SUMIF Formula in E8 with Column A as range and Column B as sum_range whereas criteria D8 will be mentioned with wildcards.
(“*”& on both sides of the criteria D8 is known as wildcards) - Enter the formula and drag it down to E11 and Excel will automatically calculate the quantities for other criteria’s.
Using SUMIF if cells contain text and numbers
Figure 3. Example 3
Formula for text and numbers:
We need to find the total price of all the shirts and pants in the given example by taking “Shirts” as one specific criteria with different numbers. Steps to follow are:
- Take column D and write down the specific words “Shirts” and “Pants” that are used with other words and figures in range.
- Take Column E for calculation of total price of all the shirts and pants in range.
- Use formula in E3 and Enter.
- Use the same formula in E4 by only replacing the word “Shirts” with “Pants”.
- We can also use in E3 and drag it to E4 and we’ll get the same results.
(The second formula given in this example is the same one that is used in example # 2.It can be used in both cases)
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
In most cases in your day-to-day data analysis, you probably just want a quick way to sum cells based on text criteria. For example, if you have a list of product sales and want to calculate the total sales by product type, or maybe to sum based on products with names containing specific text. Excel’s SUMIF function is a powerful and simple way to add up values based on criteria. In this article, you’ll learn how to use this function with text-based criteria in different situations.
How to use SUMIF with text criteria in Excel
SUMIF allows you to sum numbers based on criteria. To use SUMIF with text criteria, you can use the following general formula:
Where:
- : The range of cells that contains the criteria.
- : Text criteria, enclosed in double quotes.
- : The range of cells to add. This parameter is optional. If you don’t specify this argument, Excel adds the cells specified in the argument.
Here are some important notes:
- The text criteria is not case-sensitive.
- The length of the text criteria is limited to characters.
- You can use logical operators (, , , , , ) combined with text criteria. However, you can omit the “” operator when using “is equal to” criteria.
- You can use the text criteria with wildcards (, , ) for partial matching.
- If you want to find the sum of values with multiple criteria, use the SUMIFS function instead.
Also, keep in mind that you can also use SUMIF with dates or numbers in the criteria other than text. If you want to learn about the basics of SUMIF, check out our article on that: SUMIF function in Excel.
Basic Examples of Excel SUMIF function with text criteria
We’ll use the following order data for most of the examples used in this article. As you can see, each order has an order number, order type, customer info and the product they ordered, total order value in USD, and notes related to that particular order.
Tip: If you have data stored in PipeDrive, Shopify, Jira, or other external sources but want to do an analysis using Excel, you can import them first into Excel using Coupler.io. It’s a powerful data integration tool that lets you bring data from external sources into Excel, Google Sheets, and BigQuery for your analysis needs! Explore Microsoft Excel integration, as well as how Coupler.io works and reasons you would need Excel integration for your business.
Now, let’s see some examples of using the Excel SUMIF function to solve simple cases, such as summing if cells match specific text, if cells are empty, etc.
Excel SUMIF: If cells match specific text
In the following example, we are calculating the total for Retail orders using a SUMIF formula in C3:
Formula explanation:
=SUMIF(D6:D20,"retail",G6:G20)The formula sums the amounts in column G (range G6:G20), where the order type in column D (D6:D20) is equal to . Notice that the text is enclosed within double quotes and not case-sensitive. You can also write it as either or , and the total will be the same.
You can also refer to C2 instead of typing the text criteria manually. To do that, just replace “retail” with C2 (without double quotes):
=SUMIF(D6:D20,C2,G6:G20)Excel SUMIF: If cell does not equal text
In the following example, we calculate the total order by non-California customers.
Formula explanation:
=SUMIF(C6:C20,"<>"&C2,G6:G20)The formula calculates the total orders in range G6:G20, where the customer’s state in range C6:C20 does not equal California. Notice that in the criteria parameter, we use the operator combined with cell C2, which refers to .
Excel SUMIF: If text is empty, not empty (blank, not blank)
As you can see in the following screenshot, some orders have notes while others don’t. In this example, we are comparing the total sales for orders with empty notes vs. not empty.
Formula explanation:
We use the following formulas in C2 and C3:
- Note is empty (C2):
- Note is not empty (C3):
Notice that we use (double quotes, without any space between them) to find empty notes. To search for anything that is not empty, we use (not equal operator, wrapped in double-quotes).
Examples of Excel SUMIF with criteria: cell contains partial text
SUMIF supports using wildcards for partial matching, which makes this function powerful. You can use the following wildcard characters to perform partial searches:
Use | To find |
---|---|
(question mark) | Any single character. For example, finds and . |
(asterisk) | Any sequence of characters. For example, finds and . |
(tilde) followed by , , or | A question mark, asterisk, or tilde. A tilde in front of a wildcard character is an escape character for that wildcard. For example, finds . |
Now, let’s see some examples below for using SUMIF with wildcards.
Excel SUMIF: If cell contains specific text in any position
Suppose we want to calculate the total sales generated by customers with a name containing “Parker”, and then put the result in C2, as the following screenshot shows:
Formula explanation:
=SUMIF(B6:B20,C2,G6:G20)The SUMIF formula in C3 has the text criteria . If you notice, it finds the rows with the customer name containing “parker” in any position: start, middle, or end. If we entered the text criteria directly in the formula, we would need to enclose it with double-quotes, and the formula would look like this:
=SUMIF(B6:B20,"*parker*",G6:G20)Notice again that the criteria is not case-sensitive.
Excel SUMIF: If cell starts or ends with specific text
In the following example, we will calculate the total orders for customers whose name starts with in C2 and customers whose name ends with in C3.
Formula explanation:
We use the following formulas in C2 and C3:
- Name starts with “Olivia” (C2):
- Name ends with “Group” (C3):
To search for all text starting with , we add an asterisk at the end of the criteria: . To match all text ending in , we place an asterisk at the beginning of the criteria: .
Excel SUMIF: If cells contain an asterisk
The following example shows how to calculate the total for orders with a note containing an asterisk () in C2; whereas in C3, the formula calculates the total for orders with a note containing an asterisk at the end.
Formula explanation:
- Note contains (C2):
- Note contains at the end (C3):
The asterisk itself is a wildcard that represents any number of characters. So, if you want to find text with an asterisk in it, use a tilde () before the asterisk character, to indicate the character itself (and not a wildcard).
More advanced examples of Excel SUMIF with text criteria
We’ll see some examples of using SUMIF in Excel with multiple text criteria. In addition, we’ll see how to sum based on color using VBA.
Excel SUMIF with multiple “OR” text criteria
With the following table, suppose we want to add up the total orders made by customers from Arizona or Illinois.
Formula explanation:
=SUM(SUMIF(C6:C20, {"Arizona","Illinois"},G6:G20))Notice that in the criteria, we use an array with two text elements: . The SUMIF function can take an array in its criteria argument. But this will force the function to also return the results as an array: , which is the total orders for Arizona and Illinois, separately. This is why it’s necessary to wrap the SUMIF within SUM. The SUM does the final work by adding up each element in the array and returning , which is the correct result.
Excel SUMIFS with multiple “AND” text criteria
In the following example, let’s say we want to calculate the total Wholesale orders by customers from Kansas.
Formula explanation:
=SUMIFS(G6:G20,D6:D20,"Wholesale",C6:C20,"Kansas")In this case, we use the SUMIFS function instead of SUMIF in the formula because SUMIFS supports multiple criteria. Notice that we place the range that we want to sum in the first parameter when using this function, followed by each pair of criteria range and criteria.
Excel SUMIF based on text background color
With the following table, suppose we want to calculate the total profit based on the cell background color.
There is no default function in Excel to find the total based on a cell’s background color. One of the solutions is to create a VBA function to get the color index of the background color and use it with SUMIF. Here’s how:
- Press Alt+F11 to open the Visual Basic Editor (VBE).
- Click Insert > Module.
- Copy-paste the following function to the editor:
- Set the header of column C as “Bg Color”. Then, enter in cell C2 and copy it down to row 7.
- In E2 and E3, write the results returned by the BgColorIndex function for each background color.
- Use the following formulas to sum the total based on the criteria in E2 and E3:
- Color index is 38 (cell F2):
- Color index is 24 (cell F3):
You will get the following result:
Excel SUMIF based on text color
Now, what if we want to sum based on text color (font color)? The solution is similar to our previous example, but instead of getting the index of the background color, we’re getting the index of the text color. After that, we’ll use SUMIF to sum the total.
Here are the steps:
- Press Alt+F11 to open the Visual Basic Editor (VBE).
- Click Insert > Module.
- Copy-paste the following function to the editor:
- Set the header of column C as “Text Color”. Then, enter in cell C2 and copy it down to row 7.
- In E2 and E3, write the results returned by the TextColorIndex function for each text color.
- Use the following formulas to sum the total based on the criteria in E2 and E3:
- Color index is 3 (F2):
- Color index is 5 (F3):
You will get the following result:
Wrapping up
We’ve covered various SUMIF formulas for summarizing values based on text criteria in Excel, from basic to more complex scenarios. We hope these examples have been helpful and come in handy when you need to do a quick analysis of your day-to-day work. If you are looking to import data into Excel, BigQuery, or Google Sheets, don’t forget to try Coupler.io. This amazing tool can import data from external sources into Excel, making it easier to analyze — no coding required!
Back to BlogExcel: If cell contains then count, sum, highlight, copy or delete
In our previous tutorial, we were looking at Excel If contains formulas that return some value to another column if a target cell contains a given value. Aside from that, what else can you do if a cell contains specific text or number? A variety of things such as counting or summing cells, highlighting, removing or copying entire rows, and more.
Excel 'Count if cell contains' formula examples
In Microsoft Excel, there are two functions to count cells based on their values, COUNTIF and COUNTIFS. These functions cover most, though not all, scenarios. The below examples will teach you how to choose an appropriate Count if cell contains formula for your particular task.
Count if cell contains any text
In situations when you want to count cells containing any text, use the asterisk wildcard character as the criteria in your COUNTIF formula:
Or, use the SUMPRODUCT function in combination with ISTEXT:
In the second formula, the ISTEXT function evaluates each cell in the specified range and returns an array of TRUE (text) and FALSE (not text) values; the double unary operator (--) coerces TRUE and FALSE into 1's and 0's; and SUMPRODUCT adds up the numbers.
As shown in the screenshot below, both formulas yield the same result:
Count if cell contains specific text
To count cells that contain specific text, use a simple COUNTIF formula like shown below, where range is the cells to check and text is the text string to search for or a reference to the cell containing the text string.
For example, to count cells in the range A2:A10 that contain the word "dress", use this formula:
Or the one shown in the screenshot:
Count if cell contains text (partial match)
To count cells that contain a certain substring, use the COUNTIF function with the asterisk wildcard character (*).
For example, to count how many cells in column A contain "dress" as part of their contents, use this formula:
Or, type the desired text in some cell and concatenate that cell with the wildcard characters:
For more information, please see: COUNTIF formulas with partial match.
Count if cell contains multiple substrings (AND logic)
To count cells with multiple conditions, use the COUNTIFS function. Excel COUNTIFS can handle up to range/criteria pairs, and only cells that meet all of the specified conditions will be counted.
For example, to find out how many cells in column A contain "dress" AND "blue", use one of the following formulas:
Or
Count if cell contains number
The formula to count cells with numbers is the simplest formula one could imagine:
Please keep in mind that the COUNT function in Excel counts cells containing any numeric value including numbers, dates and times, because in terms of Excel the last two are also numbers.
In our case, the formula goes as follows:
To count cells that DO NOT contain numbers, use the SUMPRODUCT function together with ISNUMBER and NOT:
Sum if cell contains text
If you are looking for an Excel formula to find cells containing specific text and sum the corresponding values in another column, use the SUMIF function.
For example, to find out how many dresses are in stock, use this formula:
Where A2:A10 are the text values to check and B2:B10 are the numbers to sum.
Or, put the substring of interest in some cell (E1), and reference that cell in your formula, as shown in the screenshot below:
To sum with multiple criteria, use the SUMIFS function.
For instance, to find out how many blue dresses are available, go with this formula:
Or use this one:
Where A2:A10 are the cells to check and B2:B10 are the cells to sum.
Perform different calculations based on cell value
In our last tutorial, we discussed three different formulas to test multiple conditions and return different values depending on the results of those tests. And now, let's see how you can perform different calculations depending on the value in a target cell.
Supposing you have sales numbers in column B and want to calculate bonuses based on those numbers: if a sale is over $, the bonus is 10%; for sales between $ and $ the bonus is 7%; for sales between $ and $ the bonus is 5%, and no bonus for under $ sales.
To have it done, simply multiply the sales (B2) by a corresponding percentage. How do you know which percentage to multiply by? By testing different conditions with nested IFs:
In real-life worksheets, it may be more convenient to input percentages in separate cells and reference those cells in your formula:
The key thing is fixing the bonus cells' references with the $ sign to prevent them from changing when you copy the formula down the column.
Excel conditional formatting if cell contains specific text
If you want to highlight cells with certain text, set up an Excel conditional formatting rule based on one of the following formulas.
Case-insensitive:
Case-sensitive:
For example, to highlight SKUs that contain the words "dress", make a conditional formatting rule with the below formula and apply it to as many cells in column A as you need beginning with cell A2:
Excel conditional formatting formula: if cell contains text (multiple conditions)
To highlight cells that contain two or more text strings, nest several Search functions within an AND formula. For example, to highlight "blue dress" cells, create a rule based on this formula:
For the detailed steps, please see How to create a conditional formatting rule with a formula.
If cell contains certain text, remove entire row
In case you want to delete rows containing specific text, use Excel's Find and Replace feature in this way:
- Select all cells you want to check.
- Press Ctrl + F to open the Find and Replace dialog box.
- In the Find what box, type the text or number you are looking for, and click the Find All
- Click on any search result, and then press Ctrl + A to select all.
- Click the Close button to close the Find and Replace
- Press Ctrl and the minus button at the same time (Ctrl -), which is the Excel shortcut for Delete.
- In the Delete dialog box, select Entire row, and click OK. Done!
In the screenshot below, we are deleting rows containing "dress":
If cell contains, select or copy entire rows
In situations when you want to select or copy rows with relevant data, use Excel's AutoFilter to filter such rows. After that, press Ctrl + A to select the filtered data, Ctrl+C to copy it, and Ctrl+V to paste the data to another location.
To filter cells with two or more criteria, use Advanced Filter to find such cells, and then copy the entire rows with the results or extract only specific columns.
For the detailed step-by-step instructions, please see the following tutorials:
This is how you manipulate cells based on their value in Excel. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our sample Excel If Cell Contains workbook. I thank you for reading and hope to see you on our blog next week!
You may also be interested in
Sum contains cell excel if
SUMIF function is conditional if function which is used to sum the cells based on certain criteria, not the criteria can be a certain text too, for example we want to sum of group of cells if the cell adjacent to them have a specified text in them then we use function as follows =SUMIF(Text Range,” Text”, cells range for sum).
Excel Sumif Text
Sumif function in excelThe SUMIF Function is a conditional sum function that calculates the sum of given numbers based on a condition. It only uses one condition, and the syntax for using this function is =SUMIF (Range, Criteria, Sum range).read more is used if we want to find out the total of values in a cell range when another set of cell range or corresponding array satisfies particular criteria. The function can also be used to add cells that contain specific or partial text.
General Syntax for SUMIF function is as follows:
The SUMIF function syntax has the following arguments:
- Range: Required represents the values or range of cells that are required to be tested against the supplied criteria.
- Criteria: Required, represents the condition to be checked/tested against each value of the supplied range.
- [sum_range]: Optional, represents the values or range of cells that are required to be added together if the parameter ‘range’ satisfies the given condition/criteria. If this is not provided in the function, then Excel sums the cells specified in the range argument itself.
You are free to use this image on your website, templates etc, Please provide us with an attribution linkArticle Link to be Hyperlinked
For eg:
Source: Sumif Text in Excel (wallstreetmojo.com)
Examples of SUMIF Text in Excel
Let us understand Sumif Text in excel with the help of examples.
You can download this Sumif Text Excel Template here – Sumif Text Excel Template
Example #1
Let us say we have scores of an exam of students of two classes/sections: Section A and Section B, and we wish to find the total of scores of students of section A in an exam.
The scores of students are stored in the column: C, and the section of students are stored in a column: B. Then the following formula tells Excel to return a sum of the total score for all students in section A:
=SUMIF(B2:B11,&#;A&#;,C2:C11)
The function would be specified as below:
So we can see in the above screenshot that a simple SUMIF is sufficient to get results based on a particular text condition. The formula sums all the scores where the corresponding section is: ‘A.’
So the result is as follows,
The below-highlighted scores will be added to give a total of , as their corresponding section is: ‘A.’
Example #2
Now, let’s say in the above example we have one more column that specifies or identifies if the score of a student is ‘Excellent,’ ‘Good,’ ‘Bad,’ or ‘Average,’ and we wish to find the total of scores of students whose score is identified as ‘Average’:
The scores of students are stored in column C, and the identifier (e.g.: ‘Good,’ ‘Average’) is stored in column D. Then the following formula tells Excel to return a sum of the total score for all students whose score is identified as ‘Average’:
=SUMIF(D2:D11,&#;Average&#;,C2:C11)
The function would be specified as below:
So the result is as follows,
So we can see in the above screenshot that the formula sums all the scores where the corresponding identifier is: ‘Average.’
Example #3
Let’s say we have two columns containing an item and the salesperson required for the item and the total profit in the third column. Now, if we wish to find out the total profit from all the items except Hats, then we can use the SUMIF formula with a criterion which finds total if the cell value is not equal to the given condition:
So, we write the SUMIF condition as below:
=SUMIF(A2:A8,&#;<>Hat&#;,C2:C8)
The function would be specified as below:
So the result is as follows,
So we can see in the above screenshot that the formula sums all the profits except the profit corresponding to the item: Hat.
The below-highlighted profits will be added to give a total of , as their corresponding item is not ‘Hat’:
Example #4
Let’s say we have some employees with their team names and salaries. The teams are of two categories: ‘Technical,’ or ‘Operations,’ and their names begin with ‘Tech’ representing ‘Technical’ and ‘Options’ representing ‘Operations.’ Now, we wish to find out the total of salaries of technical teams. In this case, we use the wildcard ‘*’ in the SUMIF function criteria to see if the team name starts/begins with ‘Tech’:
=SUMIF(B2:B7,&#;Tech*&#;,C2:C8)
The function would be specified as below:
So we can see in the above screenshot that the formula sums all the salaries where the corresponding team names begin with ‘Tech,’ and ‘*’ is used as a wildcard in the text criteria (as above) to accomplish this task.
So the result is as follows,
Example #5
Let’s say we have some students with their scores and qualification having three categories: ‘Graduation of 3 years’, ‘Graduation of 4 years’, ‘Post Graduation,’ and they are named as: ‘Grad3’, Grad4’, and ‘PostGrad’ respectively. Now we wish to find out the total of scores of ‘Grad3’ students. In this case, we use the wildcard ‘*’ as follows:
=SUMIF(B2:B8,&#;G*3&#;,C2:C8)
The function would be specified as below:
So we can see that ‘*’ can be used to test a sequence of characters: “G*3” in the above formula tests or matches all cells containing a string that begins with ‘G’ and ends with ‘3’.So the scores where the corresponding qualification is ‘Graduation3’ are added to give a total of
So the result is as follows,
Things to Remember
- SUMIF function is a built-in function in Excel categorized as a Mathematical/Trigonometric function.
- SUMIF text is useful in cases where we wish to sum numbers in a range of cells based on text criteria.
- SUMIF function is not case-sensitive.
We see that the text criteria: ‘Average’ and ‘average’ will be treated or evaluated as the same.
- The parameter provided as ‘criteria’ to the SUMIF function can be either a numeric value (integer, decimal, logical value, date, or time), or a text string, or even an expression.
- If the parameter provided as ‘criteria’ to the SUMIF function is a text string or an expression, then it must be enclosed in double-quotes.
- The wildcards that can be used in text criteria are: ‘?’ to match a single character and ‘*’ to match the sequence of characters.
- In case we need to find an actual or literal question mark or asterisk in the supplied range, then we use a tilde (~) in front of the question mark or asterisk ( ~*, ~?).
- The logical operators in excelIn Excel, logical operators, also known as comparison operators, are used to compare two or more values. Depending on whether the condition matching is true or false, these operators return the output.read more that can be used in expression criteria are:
- Less than operator: ‘<’
- Greater than operator: ‘>’
- Less than or Equal to the operator: ‘<=’
- Greater than or Equal to the operator: ‘>=.’
- Equal to the operator: ‘=.’
- Not Equal to the operator: ‘<>.’
- Concatenate operator: ‘&’
- If the parameter provided as ‘criteria’ to the SUMIF function is a text string that is more than characters long, then the function returns ‘#VALUE!’ error.
- If we wish to find the sum of values of a range based on multiple criteria, then the ‘SUMIFS’ function is used.
Recommended Articles
This has been a guide to Sumif Text in Excel. Here we discuss examples of How to SUMIF cells that contain text in another cell along with a downloadable excel template. You may also look at these useful functions in excel –
- 35+ Courses
- + Hours
- Full Lifetime Access
- Certificate of Completion
Sumif
Numeric Criteria | Text Criteria | Date Criteria | And Criteria | Or Criteria
The powerful SUMIF function in Excel sums cells based on one criteria. This page contains many easy to follow SUMIF examples.
Numeric Criteria
Use the SUMIF function in Excel to sum cells based on numbers that meet specific criteria.
1. The SUMIF function below (two arguments) sums values in the range A1:A5 that are less than or equal to
2. The following SUMIF function gives the exact same result. The & operator joins the 'less than or equal to' symbol and the value in cell C1.
3. The SUMIF function below (three arguments, last argument is the range to sum) sums values in the range B1:B5 if the corresponding cells in the range A1:A5 contain the value
4. The following SUMIF function gives the exact same result (second argument refers to cell D1).
Text Criteria
Use the SUMIF function in Excel to sum cells based on text strings that meet specific criteria. Always enclose text in double quotation marks.
1. The SUMIF function below sums values in the range B1:B5 if the corresponding cells in the range A1:A5 contain exactly circle.
2. The SUMIF function below sums values in the range B1:B5 if the corresponding cells in the range A1:A5 do not contain exactly triangle.
3. The SUMIF function below sums values in the range B1:B5 if the corresponding cells in the range A1:A5 contain exactly circle + 1 character. A question mark (?) matches exactly one character.
4. The SUMIF function below sums values in the range B1:B5 if the corresponding cells in the range A1:A5 contain a series of zero or more characters + le. An asterisk (*) matches a series of zero or more characters.
5. The SUMIF functions below sum values in the range B1:B5 if the corresponding cells in the range A1:A5 contain exactly triangle or circle8.
Date Criteria
Use the SUMIF function in Excel to sum cells based on dates that meet specific criteria.
1. The SUMIF function below sums the sales after January 20th,
Note: the DATE function in Excel accepts three arguments: year, month and day.
2. The SUMIF function below sums today's sales.
Note: today is August 3rd,
3. The SUMIFS function (with the letter S at the end) below sums the sales between two dates.
Note: the SUMIFS function in Excel sums cells based on two or more criteria (first argument is the range to sum, followed by two or more range/criteria pairs). Adjust the dates to sum the sales in a specific month, year, etc.
And Criteria
Summing with And criteria in Excel is easy.
1. For example, to sum the cells that meet the following criteria: Google and Stanford (two criteria ranges), simply use the SUMIFS function (with the letter S at the end).
Note: remember, when using the SUMIFS function, the first argument is the range to sum, followed by two or more range/criteria pairs.
Or Criteria
Summing with Or criteria in Excel can be tricky.
1. The formula below sums the cells that meet the following criteria: Google or Facebook (one criteria range). No rocket science so far.
2. However, if you want to sum the cells that meet the following criteria: Google or Stanford (two criteria ranges), you cannot simply use the SUMIF function twice (see the picture below).
Note: cells that meet the criteria Google and Stanford are added twice, but they should only be added once. 10 is the answer we are looking for.
3. The array formula below does the trick.
Note: finish an array formula by pressing CTRL + SHIFT + ENTER. Excel adds the curly braces {}. Visit our page about Summing with Or Criteria for detailed instructions on how to create this array formula.
Now discussing:
- Marvel strike force mod menu
- 2013 ford f150 jack kit
- 1 carat emerald engagement ring
- Ati managing the team quizlet
- 2004 ford focus wheel bearing
- Solid bar and grill boise
- 2002 polaris trailblazer 250 plastics
- Private schools in boulder county
- Other words for connect
- Cars 3 nintendo switch controls
- Jenkins used car outlet
- 2014 camaro lt horsepower
- Steve morris racing engine
You use the SUMIF function to sum the values in a range that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5. You can use the following formula: =SUMIF(B2:B25,">5")
This video is part of a training course called Add numbers in Excel.
Tips:
If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."
To sum cells based on multiple criteria, see SUMIFS function.
Important: The SUMIF function returns incorrect results when you use it to match strings longer than characters or to the string #VALUE!.
Syntax
SUMIF(range, criteria, [sum_range])
The SUMIF function syntax has the following arguments:
range Required. The range of cells that you want evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored. The selected range may contain dates in standard Excel format (examples below).
criteria Required. The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added. Wildcard characters can be included - a question mark (?) to match any single character, an asterisk (*) to match any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
For example, criteria can be expressed as 32, ">32", B5, "3?", "apple*", "*~?", or TODAY().
Important: Any text criteria or any criteria that includes logical or mathematical symbols must be enclosed in double quotation marks ("). If the criteria is numeric, double quotation marks are not required.
sum_range Optional. The actual cells to add, if you want to add cells other than those specified in the range argument. If the sum_range argument is omitted, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).
Sum_range should be the same size and shape as range. If it isn't, performance may suffer, and the formula will sum a range of cells that starts with the first cell in sum_range but has the same dimensions as range. For example:
range
sum_range
Actual summed cells
A1:A5
B1:B5
B1:B5
A1:A5
B1:K5
B1:B5
Examples
Example 1
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
Property Value | Commission | Data |
---|---|---|
$, | $7, | $, |
$, | $14, | |
$, | $21, | |
$, | $28, | |
Formula | Description | Result |
=SUMIF(A2:A5,">",B2:B5) | Sum of the commissions for property values over $, | $63, |
=SUMIF(A2:A5,">") | Sum of the property values over $, | $, |
=SUMIF(A2:A5,,B2:B5) | Sum of the commissions for property values equal to $, | $21, |
=SUMIF(A2:A5,">" & C2,B2:B5) | Sum of the commissions for property values greater than the value in C2. | $49, |
Example 2
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
Category | Food | Sales |
---|---|---|
Vegetables | Tomatoes | $2, |
Vegetables | Celery | $5, |
Fruits | Oranges | $ |
Butter | $ | |
Vegetables | Carrots | $4, |
Fruits | Apples | $1, |
Formula | Description | Result |
=SUMIF(A2:A7,"Fruits",C2:C7) | Sum of the sales of all foods in the "Fruits" category. | $2, |
=SUMIF(A2:A7,"Vegetables",C2:C7) | Sum of the sales of all foods in the "Vegetables" category. | $12, |
=SUMIF(B2:B7,"*es",C2:C7) | Sum of the sales of all foods that end in "es" (Tomatoes, Oranges, and Apples). | $4, |
=SUMIF(A2:A7,"",C2:C7) | Sum of the sales of all foods that do not have a category specified. | $ |
Top of Page
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
See also
The SUMIFS function adds all arguments that meet multiple criteria
The SUMSQ function sums multiple values after it performs a mathematical square operation on each of them
The COUNTIF function counts only the values that meet a single criteria
The COUNTIFS function counts only the values that meet multiple criteria
IFS function (Office , Excel and later)
Overview of formulas in Excel
How to avoid broken formulas
Detect errors in formulas
Math & Trig functions
Excel functions (alphabetical)
Excel functions (by Category)
Using SUMIF, COUNTIF, and related functions for quick data analysis (free preview)