Search code examples
regexgoogle-sheetshighlightgoogle-sheets-formulags-conditional-formatting

How to Highlight or Select cells that are being selected by SUMPRODUCT and SUMIF?


I have a sheet with expenses where expenses are being summed by sumif one example would be

=SUMPRODUCT(sumif(A178:A,{"*vaik*","*lejos*","*kaleb*","*reju*"},C178:C))

where expenses on certain individuals are being selected and summed up.

And things don't seem to add up...

My guess is that there are some cells that have parts of words that match on(vaik,lejos,kaleb,reju) that shouldn't
The sheet is quite long and finding the false positives manually would be a colossal pain. Is there a way to show|highlight|indicate which cells are being matched by

=SUMPRODUCT(sumif(A178:A,{"*vaik*","*lejos*","*kaleb*","*reju*"},C178:C))

Or some other workaround


Solution

  • you can do:

    =REGEXMATCH(LOWER(A2), "vaik|lejos|kaleb|reju")
    

    0