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 Workbook

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.

Steps:

➤ Activate Cell C14

➤ Then type the formula given below-

➤ Hit the Enter button.

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

Read More: How to Sum Names in Excel (4 Suitable Ways)

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-

➤ Then hit the Enter button for the result.

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

Read More: How to Assign Value to Text and Sum in Excel (2 Easy Methods)

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:

➤ Later, hit the Enter button.

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

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

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:

➤ Next, press the Enter button just.

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

Read More: How to Sum Cells with Text and Numbers in Excel

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

➤ Later, just click the Enter button for the result.

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-

➤ Then just press the Enter button.

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

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-

➤ Next, just hit the Enter button.

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

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–

➤ Then press the Enter button for the output.

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

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

Read More: How to Sum Text Values Like Numbers in Excel (3 Methods)

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

➤ Finally, click the Enter button.

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

• How to Sum If Cell Contains Text in Another Cell in Excel
• Sum Only Numbers and Ignore Text in Same Cell in Excel