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.

**Table of Contents**hide

**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.

**Steps:**

**➤** Activate** Cell C14**

**➤** Then type the formula given below-

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

**➤** Hit the **Enter **button.

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.

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.

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.

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.

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.

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-

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

**➤** 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**–

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

**➤** 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 **“*~**”**.

**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.

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**