Summing up is one of the common things needed daily. While the usual summation is lengthier for large datasets, summation based on criteria is a more critical one. Excel has SUMIF and SUMIFS functions used to form formulas that can do the summation based on criteria. This helps to save both energy and time. The article will describe** 5 examples** with **5** different criteria using these functions to sum if the cell contains criteria.

Below, I have attached an overview image of this article.

**Table of Contents** hide

Download Practice Workbook

Introduction to SUMIF and SUMIFS Functions in Excel

5 Examples for Finding Sum If a Cell Contains Criteria

1. Finding Sum If a Cell Contains Specific Text

2. Calculating Sum If a Cell Contains Part of a Text String

3. Determining Sum If a Cell Contains Numbers

4. Computing Sum If a Cell Contains Text Situated in Another Cell in Excel

5. Finding Sum If a Cell Contains Asterisk

Things to Remember

Practice Section

Conclusion

Related articles

**Download Practice Workbook**

You can download the workbook from here.

**Finding Sum If Cell Contains.xlsx**

**Introduction to SUMIF and SUMIFS Functions in Excel**

**1. The SUMIF Function**

**Objective:**

Basically, it adds the cells specified by a given condition or criteria.

**Formula Syntax:**

`=SUMIF(range, criteria, [sum_range])`

**Arguments:**

** range**= the range of the data.

** criteria**= the condition based on which summation will take place.

** sum_range**= the range of data whose specific cells based on criteria will be summed up.

**2. The SUMIFS Function**

**Objective:**

Here, it adds the cells specified by a given set of conditions or criteria.

**Formula Syntax:**

`=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2],…)`

**Arguments:**

** sum_range**= the range of data whose specific cells based on criteria will be summed up.

** set of criteria_range (criteria_range1, criteria_range2…)**= the range of the data where the condition will be applied.

** set of criteria (criteria1, criteria2…)**= the condition to apply.

**5 Examples for Finding Sum If a Cell Contains Criteria**

Here, we will use the following dataset in the following examples.

Here, we can see the dataset contains** 2 columns** with the **Product Name **and **Price **of products of a company. The **Product** **Name **column contains varieties of names with texts, numbers, and asterisks. Now, in the following **5 examples**, we will discuss how to sum if these cells contain certain criteria with proper illustrations.

Furthermore, for this session, we’re going to use **Microsoft 365 version**.

**1. Finding Sum If a Cell Contains Specific Text**

Suppose you want to get the sum of the price of products having the specific text “**Hoodie**” within the name. Let us follow the steps below.

**Steps:**

- Firstly, type the following formula in
**Cell F5**:

`=SUMIF(B5:B13,"*Hoodie*",C5:C13)`

- Then, press
**ENTER**.

There are **3 names** of products having this specific text in the dataset, Thus the result shows the summation of the price of those **3 products**. Look at the picture above. 👆

Similarly, we can see results for the name of products having specific text “**Blue**”. Just type ** "*Blue*"** in the given formula instead of

**and press**

`"*Hoodie*"`

**ENTER**. The result is shown below. 👇

**🔎**** How Does the Formula Work:**

📌 Here, the** first** argument of the **SUMIF** formula is **range**. Here, **B5:B13** is the range where the condition is applied.

📌 Next, in the **criteria** part of the argument, the specific text is given. Here, we see **two** examples for two different specific texts- “**Hoodie**” and “**Blue**”. We use asterisk at the start and end of the specific word to indicate more than one character.

📌 Consequently, the** last** argument is the **sum_range**. Here **C5:C13** is the range which takethe specific cells based on the specific text, to sum up with **SUMIF** function.

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

**2. Calculating Sum If a Cell Contains Part of a Text String**

Moving forward, let’s say you want to sum up the price of products whose name contains a text which is part of the whole name. Let us follow the steps below.

**Steps:**

- Firstly, type the following formula in
**Cell F6**.

`=SUMIF(B5:B13,"*"&E6&"*",C5:C13)`

- Secondly, press
**ENTER**.

You might think that this method works with part of the text at the beginning only. 👆

However, this is not the case for this method. It works for part of text present anywhere in the text. For example, follow the next picture. 👇

This time the formula is for “**H**” instead of “**B**”.

`=SUMIF(B5:B13,"*"&E7&"*",C5:C13)`

You can observe that here the summation result is showing results for all the products having “**H**”. It does not matter whether “**H**”is at the start or end of the text.

**🔎**** How does the Formula Work:**

📌 Here, the** first** argument of the **SUMIF** formula is **range**. Here the range is **B5:B13** where the condition is applied.

📌 Next, in the **criteria** part of the argument, the specific text is given. Here we see** two** examples for two different partial texts- “**B**” and “**H**”. The asterisk is given at the start and end of the specific word. This is used to indicate more than one character.

📌 Then, the **last** argument is the **sum_range**. Here the range is **C5:C13** which takes the specific cells based on the specific text, to sum up with **SUMIF** function.

**Read More:** How to Add Specific Cells in Excel (5 Simple Ways)

**3. Determining Sum If a Cell Contains Numbers**

Here, we will find the sum of the cell values having **numbers** from the **Product Name** column. To solve this, we are going to use Excel functions such as **SUM**, IFERROR, LEFT, and FIND functions.

Now, let’s see these steps below.

**Steps:**

- First, type the following formula:

`=SUM(IFERROR(--LEFT(B5:B13,FIND(" ",B5:B13&" ")-1),""))`

- After that, press
**ENTER**. The result will look like below. 👇

**🔎**** Formula Breakdown:**

📌 If we take the **FIND** formula:

`FIND(" ",B5:B13&" ")-1`

It will find the number of characters in the texts and subtract it by 1. The resultant array will be:

`{4;4;1;4;5;2;2;9;9}`

📌 Next, if we look the** LEFT** formula with the **FIND** one:

`LEFT(B5:B13,FIND(" ",B5:B13&" ")-1)`

It will show the numeric numbers only. Other values will show the **#VALUE! **error.

The result is:

`{#VALUE!;#VALUE!;2;#VALUE!;#VALUE!;20;15;#VALUE!;#VALUE!}`

📌 After that if we see the **IFERROR** array result for the formula

`IFERROR(--LEFT(B5:B13,FIND(" ",B5:B13&" ")-1),"")`

This will show numeric values for true and for error it will show blank. The result is shown in the array format:

`{"";"";2;"";"";20;15;"";""}`

📌 Finally the result of** IFERROR** is summed to get the result using the **SUM **formula through which we will get the final result **37**.

**Read More:** How to Add Numbers in Excel (2 Easy Ways)

**Similar Readings**

- How to Sum Between Two Numbers Formula in Excel
- Sum Formula Shortcuts in Excel (3 Quick Ways)
- How to Add Multiple Cells in Excel (6 Methods)
- Excel Sum Last 5 Values in Row (Formula + VBA Code)
- How to Add Rows in Excel with Formula (5 ways)

### 4. Computing Sum If a Cell Contains Text Situated in Another Cell in Excel

Followingly, we can also get the summed result of texts in another cell.

So, follow the steps below.

**Steps:**

- At first, type the formula in
**Cell F7**as below:

`=SUMIF(B5:B13,E7,C5:C13)`

- Subsequently, press
**ENTER**. See the result in the picture. 👇

**🔎**** How Does the Formula Work:**

📌 Here, the **first** argument of the **SUMIF** formula is **range**. Here the range is **B5:B13** where the condition is applied.

📌 Next, in the **criteria** part of the argument, the text is given. Here we have “**Blue Shirt**” as the text in another cell.

📌 Finally, the** last** argument is the **sum_range**. Here the range is **C5:C13** which takes the specific cells based on the specific text, to sum up with **SUMIF** function.

**Read More:** Sum Cells in Excel: Continuous, Random, With Criteria, etc.

**5. Finding Sum If a Cell Contains Asterisk**

Finally, we can have other criteria which are if the cell contains an **asterisk (*)**.

The steps to do this are-

**Steps:**

- First, you have to type the formula in
**Cell E7**:

`=SUMIFS(C5:C13,B5:B13,"*~**")`

- Then, press
**ENTER**. Now, find the result below. 👇

**🔎**** How Does the Formula Work:**

📌 Here, the** first** argument **sum_range** of the** SUMIFS** formula is the range of data from where we will get the result. In this case, the range is **C5:C13**.

📌 Then, the **criteria_range** set is the **second** argument here. For this case, the range is **B5:B13**.

📌 Lastly, the** third** argument is the set of **criteria**. We have an **asterisk (*)** as our criteria because we need to find texts with this sign. We can write this as **~***. Again, the **asterisk** is given at the start and end of the specific word. This indicates more than one character.

**Read More:** How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)

**Things to Remember**

1. Here, you have to give a **wildcard asterisk (*)** at the start and end of the text to indicate one or more characters. Besides, you should write any text or string within the **double apostrophe ( "")** sign.

2. Moreover, the **SUMIF** function is not case sensitive while the **FIND** function is case-sensitive.

**Practice** Section

Now, you can practice by yourself.

**Conclusion**

The article evaluated **5** different criteria to sum if a cell contains criteria. The Excel formula includes functions like **SUM**, **SUMIF**,** SUMIFS**, **IFERROR**, **FIND,** and **LEFT** functions. So, we hope the article was helpful to you. If you have any query you can write in the comment section. **Also, you can visit our website Exceldemy to learn more Excel-related content. **

**Related articles**

- How to Sum Multiple Rows and Columns in Excel
- Sum to End of a Column in Excel (8 Handy Methods)
- How to Sum Only Visible Cells in Excel (4 Quick Ways)
- [Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)
- Shortcut for Sum in Excel (2 Quick Tricks)