How to Sum If Cell Contains Text in Another Cell in Excel

Here’s a dataset that contains the Order No., Item Names, Quantity and Sales of some products. We’ll use it to create sums depending on certain values in the dataset. There’s a lookup cell and a result cell below the set (see image).

Method 1 – Sum If Cell Contains Text in Another Cell Using the SUMIF Function

We will get the Total Sales of Dark Chocolate.

Steps:

Go to cell C12 and use the following formula.

=SUMIF(C5:C9,"*"&C11&"*",E5:E9)

The ranges C5:C9 and E5:E9 indicate the Item Names and Sales, respectively. C11 denotes the location of the cell containing the search keyword (Dark Chocolate).

Press the Enter key to get the Total Sales.

Method 2 – Apply the SUMIFS Function to Add up If Cell Contains Text in Another Cell

We’ll get the Total Sales of Dark Chocolate.

Steps:

Insert the following formula in C12.

=SUMIFS(E5:E9,C5:C9,"*"&C11&"*")

Hit Enter.

Method 3 – Sum If Cell Contains Specific Text in Excel Using Wildcards in the SUMIF Function

We’ll sum up the Sales of all items that contains the word Dark in the dataset.

Steps:

Insert the formula below in cell C12:

=SUMIF(C5:C9,"*DARK*",E5:E9)

Here, ‘*Dark*’ is the search keyword.

Press Enter to get the final output.

Method 4 – Insert the SUMIFS Function with Wildcard to Add Cells Containing Specific Text

We’ll sum up the sales of items that contain the word Dark somewhere in the name.

Steps:

Select cell C12.
Insert the formula below in the cell:

=SUMIFS(E5:E9,C5:C9,"*Dark*")

Press the Enter key to get the result.

Method 5 – Calculate the Total Price by Combining the SUM Function with SUMIF and SUMIFS Function

Case 5.1 – Within One Column

Let’s calculate the Total Sales of White Chocolate and Milk Chocolate.

Steps:

Go to cell C12.
Insert the formula:

=SUM(SUMIF(C5:C9,{"White Chocolate","Milk Chocolate"},E5:E9))

Press the Enter key.

The SUMIF function will search for the keywords White Chocolate and Milk Chocolate within the C5:C9 range. E5:E9 is the sum range. If a cell in the C5:C9 range contains one of the keywords, its respective cell in the E5:E9 range will get added to the result.

Read more: How to Sum Names in Excel

Case 5.2 – Among Several Columns

We’ll calculate the Total Sales of Order No. 4 which is Milk Chocolate.

Steps:

Click on cell C12.
Use the formula below in the cell:

=SUMIFS(E5:E9,B5:B9,"3",C5:C9,"Milk Chocolate")

Press the Enter button to find the desired output.

Here, E5:E9 is the sum range. B5:B9 is the range where the SUMIFS function will find the number 3. Moreover, C5:C9 refers to the range where the SUMIFS function will search for the words Milk Chocolate.