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.
https://www.xelplus.com/excel-sum-between-dates/
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.
Featured Course
Visually Effective Excel Dashboards
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
For our next example, we want to sum all the values in the “Quantity Sold” column where the “Company ID” BEGINS with the characters “10”.
Select cell G12 and enter the following formula:
=SUMIFS( $C$4:$C$18, $A$4:$A$18, F12 & ”*” )
Scenario #3 – Sum “Quantity Sold” if “Company ID” ends with specific characters
Now we want to sum all the values in the “Quantity Sold” column where the “Company ID” ENDS with the characters “10”.
Select cell G13 and enter the following formula:
=SUMIFS( $C$4:$C$18, $A$4:$A$18, ”*” & F13 )
Scenario #4 – Sum “Quantity Sold” if “Company ID” contains specific letters in the 5th and 6th 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 5th and 6th 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.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
