Excel Magic Trick 1188: SUMIFS with Wildcards to Do Approximate Criteria or “Fuzzy” Criteria

See how use asterisk wild card to add all the cells that contain the word “Red”. SUMIFS with Wildcards to Do Approximate Criteria or “Fuzzy” Criteria. Thanks to pmsocho at YouTube.

[excelisfun]

Excel Magic Trick 1188: SUMIFS with Wildcards to Do Approximate Criteria or “Fuzzy” Criteria
See how use asterisk wild card to add all the cells that contain the word “Red”. SUMIFS with Wildcards to Do Approximate Criteria or “Fuzzy” Criteria. Thanks to pmsocho at YouTube.

[MySpreadsheetLab]

Thanks Mike! I also incorporate the wildcard with functions. Here's an example...it counts how many search terms are found (when it doesn't matter how many times just that it's there at least once). Range L5:L15 contains the search terms: {=COUNT(MATCH("*"&$L$5:$L$15&"*", $B$2:$B$101, 0))}  I guess I could have also used SEARCH function as an array.

[marksmith]

Really nice. How do you link the name of your tab to a block so let's say you name your tab 1056 then you want that reference in day the heading. I've managed to do this in headers and footers but not in the spreadsheet.

[LiquidSnake]

So how would you do the opposite? I copied several rows of numbers with letters and wanted to get rid all of the non numeric and/or calculate only the numbers.

[nickboyko]

what happens if some words have red contained within themselves, but are not actually red. For example: "Predator - Blue". This would be meant as a blue colour, but wouldn't this function sum it in as a red colour too?

[joedi]

Wondering why you did not qualify for the Webby Awards? :(