Excel Sum If a Cell Contains Criteria (5 Examples)

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.

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.

SUM if a cell contains: Sample dataset

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.

Using SUMIF to get sum if a cell contains a specific test

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 "*Hoodie*" and press 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.


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.

Using SUMIF to get sum if a cell contains a part of a text

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.


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

Sum of Number from texts having both number and text

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


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

Result on based of having text in another cell

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


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

Result of cell having asterisks

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


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.


Related articles

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo