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

Sometimes you might need to add up numbers in Excel, only when a certain cell contains specific text. For example, if a product description includes the word Apple and that word is stored in a separate cell, you can total all matching sales automatically.

This method helps you filter your data based on a keyword and calculate totals automatically. That way, the formula updates whenever you change the keyword.

In this article, we’ll learn how to set it up step by step using some simple functions like SUMIF and SUMIFS. These formulas check if the text in one cell includes the keyword from another cell, and if it does, they add up the matching values.

In the following dataset, we have a random product sales list that includes item names, descriptions, and total sales. There are three main columns: Product, Description, and Sales. Column A lists the product names, Column B includes short descriptions of each item, and Column C shows the corresponding sales figures.

We’ll use this dataset to calculate the total sales for each product keyword listed in Column E. The goal is to sum the values in Column C only if the text in Column B contains the keyword from Column E. Column F is currently empty and will be used to display the total sales based on the matching descriptions.

The simplest way to sum values when a description contains a specific word stored in another cell is by using the SUMIFS function with wildcards. The asterisk * acts as a wildcard that helps find partial matches inside a text string.

This is useful when your descriptions include keywords like “apple” or “mango” in different ways such as “Fresh red apple” or “Tropical mango flavor“.

In this method, we’ll build a formula that looks at the Description column such as Column B, checks if it contains the keyword in Column E, and then adds the matching sales values in Column C.

Here’s how to do it step by step:

➤ Open your dataset in Excel.
➤ Click on cell F2, where you want to show the total sale for Apple.

➤ Type this following formula

➤ Press Enter.
➤ This will return the total sales where the description includes the word Apple. For example, total: 120 + 200 + 560 = 880

➤ Now drag the formula down to apply the same logic for the other keywords like Banana, Orange, and Mango in Column E.
➤ Each result in Column F will automatically update based on the matching word next to it.

You can also use the SUMIF function to sum values when a description contains a keyword stored in another cell. This function is slightly simpler than SUMIFS function and works well when you’re only checking if the text includes a specific word.

Like the previous method, we’ll combine SUMIF with wildcards to look for partial matches inside the Description column.

Here’s how to apply this method:

➤ Open your dataset in Excel.
➤ Click on cell F2, where you want to calculate the total sale for Apple.
➤ Type the following formula

➤ Press Enter.
➤ This will return the total of all sales where the description contains the word Apple. For example, total: 120 + 200 + 560 = 880

➤ Now drag the formula down from F2 to F5 to apply it to the other keywords: Banana, Orange, and Mango.
➤ Each cell in Column F will now display the total sales based on the keyword in the same row of Column E.

Another way to sum values when the description contains a keyword is by using the SUMPRODUCT function. This method is helpful when you want more flexibility. It checks for matches using the SEARCH function and allows partial text matching, even if the text case is different.

We’ll use SUMPRODUCT along with ISNUMBER and SEARCH to build a formula that finds the keyword and sums the corresponding sales.

Here’s how to apply this method:

➤ Open your dataset in Excel.
➤ Click on cell F2 to calculate the total sale for Apple.
➤ Type the following formula

➤ Press Enter.
➤ You’ll see the total sales where the description includes the word Apple.

➤ Drag the formula down from cell F2 to F5 to apply it to the other keywords. Each result in Column F will now show the total sales where the description contains the matching word.

Frequently Asked Questions

How to sum when a cell contains text in Excel?

To sum values when a cell contains specific text in Excel, use the SUMIF function with wildcards. This formula allows you to look for partial text matches inside a cell.
For example, if you want to sum values in column C when column B contains the word Apple, you can use: =SUMIF(B2:B12, “*apple*”, C2:C12)
This formula will check each cell in B2:B12, and if the text contains Apple, it will add the corresponding value in C2:C12.

How to use SUMIF with text?

You can sum values based on text in nearby cells by using this formula: =SUMIF(range_with_text, “text_to_match”, range_to_sum). This checks for the specified text in the text range and sums the corresponding values.

Wrapping Up

When you need to add values based on specific text in another cell, Excel provides several straightforward options. Functions like SUMIF, SUMIFS with wildcards, and SUMPRODUCT combined with SEARCH function let you find keywords in separate cells and sum the matching numbers automatically.

These formulas update automatically as you change the keywords. Try each method to find the one that fits your needs best. You can also use these formulas alongside other Excel features to create detailed reports and summaries.