This means we need to apply criteria that looks for asubstringin the item text.
All three approaches are explained below.
Note: this example embeds wildcards together with the search substring to keep things simple.
Wildcards
Excel functions like SUMIF and SUMIFS support thewildcardcharacters “?”
The table below shows some examples.
Note that wildcards are enclosed in double quotes ("") when they appear in criteria.
SUMIFS solution
One way to solve this problem is with theSUMIFS function.
The meaning of this criteria is to match the substring “hoodie” anywhere in a text string.
However, the criteria itself is identical to what we used in SUMIFS above.
The result returned by SUMIF is also the same: 22.
Case-sensitive option
As mentioned above, the SUMIF and SUMIFS functions arenotcase-sensitive.
The FIND function isalwayscase-sensitive, and returns the position offind_textas a number when found, and a #VALUE!
error when not found.
We do not need to use a wildcard like (*) because FIND automatically searches for a substring.
These are the rows where the substring “Hoodie” appears in the text.
For a more detailed explanation of FIND + ISNUMBERsee this article.
To adapt this formula to use text in cell references,see this example.
Note: InExcel 365, it’s possible for you to 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
