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.

## Leave a Comment