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

You can download the workbook from here.

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

**Read More:** **SUMIF vs SUMIFS in Excel (A Comparative Analysis)**

**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)****How to SUM with IF Condition in Excel (6 Suitable Examples)**