Sum if cell contains text in another cell

When we want to add data based on a given criteria in Excel, it is best to use SUMIF or SUMIFS.  This article will explain how to sum cells while looking up specific text in another cell.

Figure 1.  Final result: Sum if cell contains text in another cell

Formula using SUMIF: =SUMIF(B3:B10,"*"&"Blue"&"*",C3:C10)

Formula using SUMIFS: =SUMIFS(C3:C10,B3:B10,"*"&"Blue"&"*")

Setting up the Data

Here we have a list of orders in different colors.  We want to sum the orders according to color.

Figure 2.  Sample data to sum cells based on specific text in another cell

Sum Cells in Excel

In summing cells based on the text of other cells, we can use either SUMIF or SUMIFS.

SUMIF function in Excel

SUMIF sums the values in a specified range, based on one given criteria

Syntax

=SUMIF(range,criteria, [sum_range])

Where

  • Range: the data range that will be evaluated using the criteria
  • Criteria: the criteria or condition that determines which cells will be added
  • Sum_range: the cells that will be added; if left blank, “sum_range” = “range” which means that the range of data that will be added is the same range of data evaluated

SUMIFS function in Excel

Similar to SUMIF, SUMIFS also sums the values in a specified range, but it can be based on one or more specified criteria.

Syntax

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

  • Sum_range: the cells that will be added
  • Criteria_range1: the data range that will be evaluated using the criteria
  • Criteria1:  the criteria or condition that determines which cells will be added
  • Criteria_range2 and criteria2 are optional; only applied when there are more criteria as specified

Sum If Color is Blue using SUMIF

In cell F3, enter the formula:

=SUMIF(B3:B10,"*"&"Blue"&"*",C3:C10)

This formula returns the sum of all orders in column C with any variant of color blue in column B.  As shown below,  the sum of 170, 50, 350, 140 and 90 is 800.  

Figure 3.  Entering the formula for SUMIF to sum orders of color Blue

Important Notes:

  • We want to add all orders in any variant of color blue.  Hence, our criteria should be *Blue* which includes all cells which may have texts before or after the word blue.
  • This part of the formula: “*”&”Blue”&”*” means *Blue*
  • The asterisk * is used as a wildcard in Excel, which means any number of characters
  • The ampersand “&” is used to concatenate texts or strings

Alternative Formula

In cell E4, enter the text string “ *Blue* ”.

In cell F4, enter the formula:

=SUMIF(B3:B10,E4,C3:C10)

This formula also sums all orders in column C that contains the text Blue in column B.  Putting asterisks in both sides of the text Blue and using *Blue* as the criteria simplifies the formula.

Figure 4.  Simpler formula for SUMIF to sum orders of color Blue

Sum If Color is Blue using SUMIFS

In cell F3, enter the formula:

=SUMIFS(C3:C10,B3:B10,"*"&"Blue"&"*")

In cell F4, we use the alternative formula with reference to the criteria *Blue* in cell E9

=SUMIFS(C3:C10,B3:B10,E9)

Figure 5.  Comparison of SUMIF and SUMIFS in summing orders with color Blue

Both the SUMIF and SUMIFS functions return the same results. Just be mindful of the difference in syntax of these two functions. Remember, SUMIF can only be used if there is one criterion, while SUMIFS can be used with one or more criteria.  

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar