How to Use SUMIF with Text in Excel (9 Easy Ways)

In our day-to-day life, we probably just want to sum cells quickly based on text criteria. Like, if you have a list of products and want to calculate the total profit by product type, or maybe to sum with names that contain specific text. Excel SUMIF function is a powerful tool to do that. This article will guide you on how to use this function based on a specific text.


Download Practice Book

You can download the free Excel template from here and practice on your own.


9 Easy Ways to Use SUMIF with Text in Excel

Method 1: SUMIF with a Specific Text

Let’s get introduced to our dataset first. I have arranged my dataset with some products’ names and profits. Now I’ll use the SUMIF function to sum the profit for the product “Shirt”. The SUMIF function is used to sum cells that meet specific criteria.

SUMIF with a Specific Text

Steps:

Activate Cell C14

Then type the formula given below-

=SUMIF(B5:B11,"*Shirt*",C5:C11)

Hit the Enter button.

SUMIF with a Specific Text

Now you will notice that the profit for Shirt item is summed up.


Method 2: SUMIF with Cell Reference of a Text in Excel

Now we’ll do the same operation as the previous method just using cell reference. Let’s have a look at the item Shirt in Cell C13. I’ll use this cell reference for my formula.

Steps:

In Cell C14 write the formula given below-

=SUMIF(B5:B11,"*"&C13&"*",C5:C11)

Then hit the Enter button for the result.

SUMIF with Cell Reference of a Text in Excel

Soon after you will spot that the operation using the cell reference is done.


Method 3: Apply Excel SUMIFS Function with Specific Text

Now we’ll use the SUMIFS function to sum cells with specific text. Again we’ll find the sum of profit for the Shirt item using the SUMIFS function. The SUMIFS function is used to sum cells that meet multiple criteria.

Steps:

Write the formula in Cell C14:

=SUMIFS(C5:C11,B5:B11,"*Shirt*")

Later, hit the Enter button.

Excel SUMIFS Function with Specific Text

Then you will get the expected result like the image below.


Method 4: Use of SUMIFS with Multiple AND Criteria in Excel

For this method, I have added a new column named “Salesperson”. We’ll again use the SUMIFS function to sum the cells that meet the following criteria: Hat and Tom.

Steps:

Type the formula in Cell D15:

=SUMIFS(D5:D11,B5:B11,"*Hat*",C5:C11,"Tom")

Next, press the Enter button just.

SUMIFS with Multiple AND Criteria in Excel

Now you will observe that the calculation with AND criteria is done.


Method 5: Use of SUMIF with Multiple OR Criteria in Excel

Here, we’ll sum the profit with OR criteria using the SUMIF function. Actually, the SUMIF function will work separately for the product “Hat” and the salesperson “Tom”.

Steps:

Type the formula in Cell D15

=SUMIF(B5:B11,"*Hat*",D5:D11)+SUMIF(C5:C11,"Tom",D5:D11)

Later, just click the Enter button for the result.

SUMIF with Multiple OR Criteria in Excel

Soon after you will notice the sum with OR criteria.


Method 6: Use of SUMIF When Cells Start with Specific Text in Excel

Suppose you want to sum the profits for the products which start with specific text then it is possible to do with Excel Wildcard. Wildcard characters in Excel are some special characters that are used to take the place of characters in a formula. Here, we’ll sum up the profits for the products that start with “Red”.

Steps:

After activating Cell C14 type the formula as given below-

=SUMIF(B5:B11,"Red*",C5:C11)

Then just press the Enter button.

SUMIF When Cells Start with Specific Text in Excel

Now you will get the output as in the image below.

SUMIF When Cells Start with Specific Text in Excel


Method 7: SUMIF When Cells End with Specific Text in Excel

Also, we can sum if cells end with specific text using the Excel Wildcard. We’ll sum the profits of those products which end with Hat.

Steps:

Activate Cell C14 and write the given formula-

=SUMIF(B5:B11,"*Hat",C5:C11)

Next, just hit the Enter button.

SUMIF When Cells End with Specific Text in Excel

Then you will notice that we have got our expected output.

SUMIF When Cells End with Specific Text in Excel


Method 8: Excel SUMIF with Text and Asterisk

In this method, we’ll sum only the profits of those products which contain an Asterisk using the SUMIF function. Asterisk (*) represents any number of characters. For example “Sh*” returns Shirt or Short. Tilde(~) is used to indicate the asterisk and question mark characters as they are, as * or ?, instead of a wildcard character in the formula. For example, “Sh~*” returns Sh* but not Shirt or Short. “*~**” in our formula means the SUMIF function will find the Asterisk(*) in any position of a cell, if found then the function will sum the related profits of those cells.

Steps:

Write the formula given below in Cell C13

=SUMIF(B5:B11,"*~**",C5:C11)

Then press the Enter button for the output.

Excel SUMIF with Text and Asterisk

Now you will spot that we have summed the profits of those cells which contain Asterisk.

Excel SUMIF with Text and Asterisk

Note: To sum the profit of the cells where Asterisk is at the end of the text only, then just use “*~*” instead of “*~**”.


Method 9: SUMIF with Text and Question Mark for Missing Character in a Specific Position

In our last method, we’ll learn to sum using a question mark (?). Question mark(?) represents one single character. For example, “H?t” returns hat, hut, or hot. You will notice in my updated dataset, there are two kinds of jackets those are- Jacket1 and Jacket2. We can sum the related profits of those cells using a question mark(?) by typing it after Jacket in the SUMIF formula.

Steps:

Write the formula in Cell C14

=SUMIF(B5:B11,"Jacket?",C5:C11)

Finally, click the Enter button.

SUMIF with Text and Question Mark for Missing Character in a Specific Position

Soon after you will observe that we have found our expected result.


Conclusion

I hope all of the methods described above will be good enough to sum if cells contain specific text. Feel free to ask any questions in the comment section and please give me feedback.


Related Articles

SUMIF with Multiple Criteria for Different Columns in Excel

SUMIFS Multiple Criteria Along Column and Row in Excel

SUMIFS with Wildcard in Excel + 3 Alternative Formulas

Excel SUMIF with Partial Match (3 Ways)

Sum If a Cell Contains Text in Excel (6 Suitable Formulas)

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Excel is an amazing softwear. Here i will post excel related useful articles. I am a graduate from Bangladesh University of Engineering and Technology. I love to learn new things and work with it. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo