SUM Based on Partial Text Match in Excel (SUMIF)

Excel has the SUMIF and SUMIFS functions that allow you to add a range of cells based on one or more criteria.

And sometimes, you may want to get the sum of cells based on partial match in the dataset.

For example, suppose I have a dataset as shown below and I want to get the sum of all the cells where the country in cells in column A is US.

As you can see, I need to check each cell and see if the string “US” appears in the cell or not, and if it does, I need to include the value in the adjacent cell in column B while doing the sum.

This is a case of a partial match, where I need to sum cells based on the partial match.

And thanks to wildcard characters in Excel, this is possible (I would even say it’s fairly easy).

So, let me show you some examples of how to sum cells based on partial text match using SUMIF and Wildcard characters.

A little about Wildcard Characters

Before I get into the mechanics and show you how to add cells that have a partial text match, let me quickly explain what wildcards are.

There are three wildcard characters in Excel:

• Asterisk (*) – An asterisk represents any number of characters in Excel. So, if you want to add all the cells where the string ends with US, you can use “*US” as the criteria. Here, as long as the string in the cell has US in the end, it can have any number of characters in the beginning.
• Question Mark (?) – A question mark represents one single character. So if I use the criteria as “?US”, only those cells that have three characters in the string and end with the word US would satisfy the criteria.
• Tilde (~) – It is used to identify a wildcard character (~, *, ?) in the text. It’s less used, and we won’t need it in this tutorial (it’s rarely used, and I have never used it)

Examples to SUM Cells Based on Partial Text Match

Now, let me show you some practical examples where you can use a combination of Excel formulas and wildcard characters to get the sum when there is a partial match.

SUM Cells When Partial Text Matches the End of the String

Below, I have a dataset where I have some names in column A, along with their country at the end (after the name).

I want to add the values in column B for only those cells where the country in cells column A is the US.

Since there are different names in column A, I will have to use the SUMIF function with a wildcard to add only the cells for names that have the US as the country.

Below is the formula that will do this:

=SUMIF(A2:A16,"*US",B2:B16)

In the above SUMIF formula, I have used the below arguments:

• A2:A16 – This is the range where the formula checks for the criteria. In our example, these would be the cells with names.
• “*US” – This is the criteria that is used to check every cell, and only those cells that satisfy these criteria are considered. Since I want to get the sum of all the cells in B2:B16, where the cells in A2:A16 ends with US, I have used “*US”. Asterisk (*) means that there could be any number of characters before the string US. Also, remember to have the criteria in double-quotes.
• B2:B16 – This is the range that has the values that we want to sum

Also read: Check IF Cell Contains Partial Text in Excel (Formulas)

SUM Cells When Partial Text Matches the Starting of the String

Below, I have a dataset where I have some names in column A, along with their country in the beginning (before the name).

I want to add the values in column B for only those cells where the country in cells column A is the US.

I can do this using a SUMIF formula along with a wild card character (asterisk).

Below is the formula that will do this:

=SUMIF(A2:A16,"US*",B2:B16)

Below are the arguments I have used in the above SUMIF formula:

• A2:A16 – This is the range where the formula checks for the criteria (cells with names in our example)
• “US*” – Since I want to get the sum of all the cells in B2:B16, where the cells in A2:A16 start with US, I have used “US*”. This means that any cell that starts with the string US will satisfy the criteria, no matter what comes after it
• B2:B16 – This is the range that has the values that we want to sum

Also read: How to Use VLOOKUP with Multiple Criteria

SUM When Cell Starts and Ends with Specific Text

Below, I have a data set where I have names in column A and their sales values in column B, and I want to get only the sum of sales values where the name starts with US and ends with A.

Here is the formula that will do this:

=SUMIF(A2:A16,"US*A",B2:B16)

In the above formula, I have used the following arguments:

• A2:A16 – This is the range where the formula checks for the criteria (names in column A in our example)
• “US*A” – This is the criteria that would only be satisfied in those cells that start with ‘US’ and end with ‘A’. An asterisk symbol in the middle represents that there could be any number of characters between US and A.
• B2:B16 – This is the range that has the values that we want to sum

Also read: Compare Two Columns in Excel (for matches & differences)

SUM When a String Appears Anywhere in the Cell

Now, let’s look at an example where you want to get the sum of cells where a specific text string can appear anywhere in the cell.

Below, I have a data set where I have names in column A, and I want to get the sum of their sales for only those names that have the country as the US.

Here is the formula that will do this:

=SUMIF(A2:A16,"*US*",B2:B16)

In the above formula, I have used the following arguments:

• A2:A16 – This is the range where the formula checks for the criteria (names in column A)
• “*US*” – This criteria that would only be satisfied in those cells that has the string US anywhere in the cell. Since I’ve added an asterisk symbol before and after the string US, It means that there could be any number of characters before or after US.
• B2:B16 – This is the range that has the values that we want to sum

Also read: Multiple Criteria in Excel COUNTIF / COUNTIFS Function

SUM Based on Multiple Criteria and Partial Match

Let’s now look at a little more complicated example where I want some cells based on multiple criteria and a partial match.

Below I have a data set where I have names in column and their category in column B and I want to get the sum of all the cells where the country is US and the category is A.

Since there are two conditions that I need to check for, I will have to use a SUMIFS formula with the asterisk wildcard character.

Below is the formula that will do this:

=SUMIFS(C2:C16,A2:A16,"*US",B2:B16,"A")

In the above SUMIFS formula, I have used the following arguments:

• C2:C16 – This is the range that has the values that we want to add
• A2:A16 – This is the range where I need to check for the first criteria. In this example, it is the range that contains the name where I want to check whether it ends with US or not.
• “*US” – This is the criteria that would be checked against the range A2:A16 (which is the argument before it in the formula)
• B2:B16 – This is the range where I need to check for the second criterion. In this example, it has the category codes.
• “A” – This is the second criterion that we need to check for

Also read: How to Sum by Color in Excel

SUM Cells that Contain Asterisk Character

A less common but possible situation could be when you have to get the sum of all the cells where another cell contains the * character.

The problem with this is that when I use an asterisk symbol as a criteria in my SUMIF or SUMIFS function, it would be treated as a wild card character and would represent any number of characters.

Let’s see how to tackle this situation.

Below, I have a data set where I have names in column A, and I only want to get the sum of their sales in column B if the name has an asterisk symbol in the cell.

Here is a formula that will do this:

=SUMIF(A2:A16,"*~*",B2:B16)

In the above SUMIFS formula, I have used the following arguments:

• A2:A16 – This is the range that has the names
• “*~*“ – This is the criterion. Here, I have used *~*. When I add a ~ character before the * symbol, It will treat it as any other character instead of a wild card. So when my criteria is *~*, The first asterisk symbol represents any number of characters, and ~* represents just the asterisk character at the end.
• B2:B16 – This is the range that has the numbers that I need to add based on the condition

In this article, I have covered some examples to show you how to Sum cells based on partial text match using SUMIF or SUMIFS function.

I hope you found this article useful.

I would love to know your thoughts in the comments section below.

Other Excel articles you may also like:

• How to Combine Duplicate Rows and Sum the Values in Excel
• How to Count Cells that Contain Text Strings
• How to Sum Across Multiple Sheets in Excel? (3D SUM Formula)
• How to SUM Values Between Two Dates (using SUMIFS formula)