# Excel SUM based on

# Partial Text Match

# (SUMIFS with Wildcards)

Learn how to SUM values in Excel based on a partial text match.

This is great for cases when you need to sum a column based on “criteria contains” a specific value or text.

For these cases you can use Excel’s SUMIF or SUMIFS function together with wildcards.

I also show you how you can sum based on two or more conditions. Here the SUMIFS formula comes in really handy.

This post will demonstrate how to perform a filtered sum of values based on a partial text match.

Excel will allow you to perform filtered aggregations with the following functions:

**SumIFs**– sum values based on one or more criteria**AverageIFs**– average based on one or more criteria**CountIFs**– count based on one or more criteria**MaxIFs**– find the largest value in a set based on one or more criteria**MinIFs**– find the smallest value in a set based on one or more criteria

Although these are fantastic functions, most users are under the impression that the criteria must match entirely in order to be included in the aggregation. Although, this is how these functions are typically used, this is not the case.

I have covered the mechanics of filtered aggregations (**SumIFs**, **CountIFs**, and **AverageIFs**) in a previous post. *Click the link below to review.*

https://www.xelplus.com/sumifs-countifs-averageifs/

I have also covered a technique to sum values between two dates using the **SumIFs** and **EOMonth** functions. *Click the link below to review.*

## Introducing Wildcards

Wildcards represent “any characters” and are useful when you want to capture multiple items in a search based on a pattern of characters. There are two wildcard characters:

**Asterisk (*)** – This wildcard character will allow for any character(s) in any quantity.

Example 1: Exc* (any text starting with “Exc”)

Example 2: *el (any text ending with “el”)

Example 3: *xce* (any text with “exc” anywhere in the text; beginning, middle, or end)

Example 4: Exc*el (any text starting with “Exc”, ending with “el”, and any character in between)

**Question Mark (?)** – This wildcards character will allow for any character in a single character position. This wildcard is useful when you must locate something in a specific position within a set of text.

Example 1: ??cel (The first and second characters can be anything, but the third through fifth characters must be “cel”)

Example 2: Ex?el (The first and second characters must be “Ex”, the fourth and fifth characters must be “el”, but the third character can be anything)

## Wildcards with Aggregation Functions

We can leverage the power of wildcards to create aggregation functions that are not so critical about the comparative, but rather more forgiving.

These strategies work with all the defined aggregation functions listed above. For this post, we will demonstrate various techniques using the **SUM** function.

Examine the sample data set.

## Scenario #1 – Sum “Quantity Sold” if “Company ID” contains specific characters

For our first example, we want to sum all the values in the “Quantity Sold” column where the “Company ID” contains the characters “AT” anywhere in the text; beginning, middle, or end.

Select cell **G6** and enter the following formula:

=SUMIFS( $C$4:$C$18, $A$4:$A$18, F6 )

Observe that this fails to return any “hits” because it is looking for “Company ID’s” that exactly match the letters “AT”.

We want the formula to be more forgiving.

Update the formula in cell **G6** with the following modification:

=SUMIFS( $C$4:$C$18, $A$4:$A$18, **”*” & **F6 **& ”*” **)

By placing an asterisk both in front and following the text in cell **F6**, we have told the SumIFs that the characters “AT” can occur anywhere within the “Company ID”. Because we are searching for text, we must enclose the asterisks (*****) in double-quotes and concatenate the components with ampersands (**&**).

Fill the formula from cell **G6** down to cell **G9**.

## Scenario #2 – Sum “Quantity Sold” if “Company ID” begins with specific characters

## Scenario #4 – Sum “Quantity Sold” if “Company ID” contains specific letters in the 5^{th} and 6^{th} character position

Now we want to sum all the values in the “Quantity Sold” column where the “Company ID” contains the characters “10” in the __5 ^{th} and 6^{th} character position__.

Because we want to examine each character position separately, the question mark (?) wildcard is ideally suited for this task. Because there may be other characters after character position 6, and we don’t care what or how many there are, we will use the asterisk (*) wildcard to handle the “right side” of the search.

Select cell **G14** and enter the following formula:

=SUMIFS( $C$4:$C$18, $A$4:$A$18, ”????” & F14 & ”*” )

## Scenario #5 – Sum “Quantity Sold” if “Company ID” begins with and ends with specific characters.

In this scenario, we have a set of four characters (ex: AT30) and we want to sum all the values in the “Quantity Sold” column where the “Company ID” begins with the first two characters (“AT”) and ends with the last two characters (“30”).

We will use the text functions **LEFT** and **RIGHT** to assist with the text search.

Select cell **G17** and enter the following formula:

=SUMIFS( $C$4:$C$18, $A$4:$A$18, LEFT(F17,2) & ”*” &RIGHT(F17,2) )

In the example in cell **G17**, it would be as if we were searching for “**AT*30**”.

Fill the formula from cell **G17** down to cell **G19**.

## Scenario #6 – Sum “Quantity Sold” if “Company ID” begins with specific characters but also where the “Customer” matches a specific value

In this final scenario, we will perform a similar operation as above where we want to look for “Company IDs” what begin with two specific characters but add an additional parameter where the corresponding “Customer” matches a stated customer number.

Select cell **H23** and enter the following formula:

=SUMIFS( $C$4:$C$18, $A$4:$A$18, F23 & ”*”, $B$4:$B$18, G23 )

Fill the formula from cell **H23** down to cell **H26**.

## Practice Workbook

Feel free to Download the Workbook HERE.

**Excel Dashboards that Inform & Impress**

PGSystemTesterSeptember 13, 2019 at 8:47 pmAdd AverageIF and AverageIf(s) to the list of formulas this can be applied to (I forgot too).