The challenge in this case is that the state abbreviations areembeddedin a text string.
This means we need to construct criteria that performs a “contains” jot down match.
To solve this problem, you could use either the SUMIF function or the SUMIFS function with awildcard.
If you need a case-sensitive formula, you could use the SUMPRODUCT function with the FIND function.
All three approaches are explained below.
Note: this example pulls together a number of ideas, which makes it more advanced.
It also means that the formula will continue to work correctly if new data is added to the table.
Wildcards
Excel functions like SUMIF and SUMIFS support thewildcardcharacters “?”
The table below shows some possible wildcard configurations.
Note that wildcards are enclosed in double quotes ("") when they appear in criteria.
Also notice that cell references arenotenclosed in double quotes.
Instead, they areconcatenatedto wildcards.
SUMIFS solution
One way to solve this problem is with theSUMIFS function.
This is important partly because the SUMIFS and SUMIF function arenotcase-sensitive.
Also notice that the cell reference E5 isnotenclosed in double quotes.
Instead, it isconcatenatedto wildcards on either side.
However, the criteria is identical to what we used in SUMIFS above.
Case-sensitive solution
As mentioned above, the SUMIF and SUMIFS functions arenotcase-sensitive.
error when not found.
These are the rows where the state is “CA”.
For a more detailed explanation of FIND + ISNUMBERsee this article.
Note: InExcel 365, you could replace SUMPRODUCT with theSUM function.
To read more about this, seeWhy SUMPRODUCT?
Criteria can be applied to dates, numbers, and text.
SUMPRODUCT Function
The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.
When the text is not found, FIND returns a #VALUE error.
Related videos
How to use the SUMIF function
How to use the SUMIFS function
