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.

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

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

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 Sum If a Cell Contains Criteria**

We will use the following dataset in the following examples.

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

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

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

**Steps:**

- Type the following formula in
**Cell F5**:

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

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

📌 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 specific texts- “**Hoodie**” and “**Blue**”. The asterisk is given at the start and end of the specific word. This is used to indicate more than one character.

📌 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 If a Cell Contains Text in Excel (6 Suitable Formulas)**

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

- Type the following formula in
**Cell G5**.

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

- 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,"*"&F7&"*",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:**

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

📌 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. Sum If a Cell Contains Numbers & Text**

Furthermore, you might want to get the sum of the price of the products having **both numbers and texts**. To solve this, we are going to use Excel functions such as **SUM**, **IFERROR**, **LEFT**, and **FIND** functions.

Let’s see these steps below.

**Steps:**

- Type the following formula:

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

- 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. Sum If a Cell Contains Text in Another Cell in Excel**

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

Follow the steps below.

**Steps:**

- Type the formula in
**Cell G7**as below:

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

- Press
**Enter**. See the result in the picture. 👇

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

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

📌 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. Sum If a Cell Contains Asterisk**

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

The steps to do this are-

**Steps:**

- You have to type the formula in
**Cell F7**:

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

- Then, press
**Enter**. Find the result below. 👇

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

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

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

📌 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. 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. The **SUMIF** function is not case sensitive while the **FIND** function is case-sensitive.

**Conclusion**

The article evaluated 4 different criteria to sum if a cell contains criteria. The Excel formula includes functions like **SUM**, **SUMIF**,** SUMIFS**, **IFERROR**, **FIND,** and **LEFT** functions. I hope the article was helpful to you. If you have any query you can write in the comment section.