Values that meet one or more criteria can be added up with the SUMIFS function.
The sum of values next to cells containing specific text, i.e., a sum based on text criteria, is possibly the most common use case.
The sum of values next to cells containing (non) specific text of a defined length (the defined number of characters in a text string) is a far less common need, but still crucial in specific situations.
We can accomplish this task by exploiting the wildcard character question mark ?.
The wildcard characters asterisk * and question mark ? in the SUMIFS criteria are usually used to find matches that are similar but not exact.
With an asterisk * we can match any sequence of characters.
With a question mark ? we can match any single character.
They can be combined, i.e., criteria such as “*8??” are valid.
If we need to match an actual asterisk * or a question mark ?, we can do this with a tilde ~.
Consider the following example:
In this example of our customers with names of different lengths, we want to know how much customers with three-letter names owe us. As demonstrated with the help of the LEN function, there are two such names.
We can sum the numbers in the column Amount when the text strings in the column Name are three characters long with this formula:
By exploiting the fact that a question mark ? can match any single character, and only any single character, we are forcing the SUMIFS function to consider only 3-character long names.
The same applies to related functions that perform calculations when criteria are met.
For example, the COUNTIFS function can be used in order to count values adjacent to cells containing (non) specific text of a certain length (the number of characters in a text string).
Here, we will count cells in the column Amount containing numbers when the text strings in the column Name are three characters long with this formula:
This type of matching becomes crucial in accounting and finance when financial accounts come into play. In many charts of accounts, account numbers will contain only numerical characters, and leading zeros are common. When presented in Excel, those numbers will be stored as text strings in order to preserve the leading zeros.
Functions such as the MATCH function will not encounter issues with this type of data and can be used without reservations. However, SUMIFS and related “IFS” functions will encounter issues.
“SUMIFS is not always correct” issue will often arise when account numbers are criteria, and it is present in all iterations of Excel supporting the SUMIFS function to this day.
It can be easily demonstrated in the following way:
As visible, even though account 00001500 has two more leading zeros than account 001500, both accounts are considered to meet the criteria and the incorrect sum is returned.
We can correct this issue by adding additional criteria that must be matched in the column Account, i.e., text string length:
Naturally, the manual addition of text string length criteria is not practical and can be easily automated.
We can do that by first creating a sufficiently long text string consisting of question marks ?, and then nesting that string inside of the LEFT function. The number of characters we need to return can be generated by returning the original criteria text string length with the help of the LEN function:
LEFT(“????????????????????”;LEN(same_as_the_first_criteria))
Both of our criteria are now referring to the same cell:
The same formula can now be used in order to return the sum for any given account.
Dig deeper:
SUMIFS criteria: formulas, functions and conditional statements
3 thoughts on “SUMIFS by text string length”