And if we give a shot to use two SUMIFS formulas (i.e.
One solution is to use the SUMPRODUCT function together with the ISNUMBER and SEARCH functions.
TheSEARCH functionis designed to find a specific substring in a text string.
If SEARCH finds the substring, it returns apositionof the substring in the text as a number.
If the substring is not found, SEARCH returns a #VALUE error.
For example:
To force a TRUE or FALSE result, we can use theISNUMBER function.
ISNUMBER returns TRUE for numeric values and FALSE for anything else.
For a more detailed explanation of this approach,see this page.
On the right, SEARCH is configured to look for “blue”.
Next, we add these arrays together.
We use addition (+) becauseaddition corresponds to OR logic in Boolean algebra.
Note: InExcel 365, you’re able to replace SUMPRODUCT with theSUM function.
To read more about this, seeWhy SUMPRODUCT?
Case-sensitive option
The SEARCH function ignores case.
If you need a sensitive option, you could replace the SEARCH function in this formula with theFIND function.
SUMPRODUCT Function
The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.
When the text is not found, FIND returns a #VALUE error.
SEARCH Function
The Excel SEARCH function returns the location of one text string inside another.
SEARCH returns the position offind_textinsidewithin_textas a number.
SEARCH supports wildcards, and isnotcase-sensitive….
