5 Ways To Sum In Excel With Partial Text

When working with Excel, it's common to encounter situations where you need to sum values based on partial text matches. Whether you're dealing with product codes, names, or descriptions, being able to sum values based on specific text criteria can be incredibly useful. In this article, we'll explore five ways to sum in Excel with partial text, using a combination of formulas and functions to help you achieve your goals.

Understanding the Problem

Imagine you have a dataset with product names, sales amounts, and product codes. You want to sum up the sales amounts for products that contain a specific text string, such as "Widget" or "Gadget". However, the product names may vary, and you can't rely on exact matches. This is where partial text matching comes in handy.

Method 1: Using the SUMIF Function

The SUMIF function is a powerful tool for summing values based on specific criteria. To use it for partial text matching, you can use the * wildcard character to match any characters before or after the specified text.

Formula: =SUMIF(A:A,"*Widget*",B:B)

Assuming your product names are in column A and sales amounts are in column B, this formula will sum up the sales amounts for products that contain the text "Widget".

How it Works

• The A:A range specifies the column containing the product names.
• The "*Widget*" criteria specifies the text to match, with the * wildcard characters matching any characters before or after the text "Widget".
• The B:B range specifies the column containing the sales amounts to be summed.

Method 2: Using the SUMIFS Function

The SUMIFS function is similar to the SUMIF function, but it allows you to specify multiple criteria ranges and criteria.

Formula: =SUMIFS(B:B,A:A,"*Widget*",C:C,"North")

Assuming your product names are in column A, sales amounts are in column B, and regions are in column C, this formula will sum up the sales amounts for products that contain the text "Widget" and are located in the "North" region.

How it Works

• The B:B range specifies the column containing the sales amounts to be summed.
• The A:A range specifies the column containing the product names.
• The "*Widget*" criteria specifies the text to match, with the * wildcard characters matching any characters before or after the text "Widget".
• The C:C range specifies the column containing the regions.
• The "North" criteria specifies the region to match.

Method 3: Using the INDEX-MATCH Function Combination

The INDEX-MATCH function combination is a powerful tool for looking up values in a table. To use it for partial text matching, you can use the MATCH function to find the relative position of the text, and then use the INDEX function to return the corresponding value.

Formula: =INDEX(B:B,MATCH("*Widget*",A:A,0))

Assuming your product names are in column A and sales amounts are in column B, this formula will return the sales amount for the first product that contains the text "Widget".

How it Works

• The B:B range specifies the column containing the sales amounts.
• The MATCH function finds the relative position of the text "Widget" in the product names, using the * wildcard characters to match any characters before or after the text.
• The INDEX function returns the value at the relative position found by the MATCH function.

Method 4: Using the FILTER Function (Excel 365 and Later)

The FILTER function is a new function in Excel 365 and later versions that allows you to filter a range of values based on specific criteria.

Formula: =SUM(FILTER(B:B,ISNUMBER(SEARCH("Widget",A:A))))

Assuming your product names are in column A and sales amounts are in column B, this formula will sum up the sales amounts for products that contain the text "Widget".

How it Works

• The B:B range specifies the column containing the sales amounts.
• The FILTER function filters the sales amounts based on the criteria specified in the ISNUMBER and SEARCH functions.
• The ISNUMBER function checks if the result of the SEARCH function is a number (i.e., if the text "Widget" is found in the product names).
• The SEARCH function finds the text "Widget" in the product names, returning a number if found and an error if not.

Method 5: Using Power Query (Excel 2010 and Later)

Power Query is a powerful tool in Excel that allows you to manipulate and transform data. To use it for partial text matching, you can create a new query that filters the data based on specific criteria.

Formula: =Table.SelectRows(#"Filtered Data", each Text.Contains([Product Name], "Widget"))

Assuming your product names are in column A and sales amounts are in column B, this formula will filter the data to include only products that contain the text "Widget".

How it Works

• The Table.SelectRows function filters the data based on the criteria specified in the Text.Contains function.
• The Text.Contains function checks if the product name contains the text "Widget".
• The #"Filtered Data" range specifies the data to be filtered.

Gallery of Partial Text Matching in Excel

Partial Text Matching in Excel

We hope this article has helped you learn five ways to sum in Excel with partial text. Whether you're using the SUMIF function, SUMIFS function, INDEX-MATCH function combination, FILTER function, or Power Query, you now have the tools to tackle even the most complex partial text matching tasks. Do you have any questions or need further clarification? Let us know in the comments!