Search code examples
arraysgoogle-sheetswildcardsumifs

is there a way to make SUMIF with array criteria in wildcards sums only once even though the cell has all the criteria of the array?

I want to sum cells that has specific texts on them. I tried a lot of things with SUMIF(). Now I combined the SUMPRODUCT() function and I got closed to what I want. Using the functions SUMPRODUCT() and SUMIF() combined, having the texts that I want in a array and using wild cards on them I can have what I want but it doesn't work if the cell has more than one of the texts of the array, because it sums multiple times. What I want is to sum only once even though the cell has more than one of the texts of the array

I have two columns, one with the name of the product, other with the value of the product. I want to see the total sum of specific items so I used this formula in the cell C95:

=SUMPRODUCT(SUMIF(A92:A99,{"*apple*" ,"*orange*", "*banana*"},B92:B99))

The result is what I expect when the cells only have one of the criteria:

enter image description here

But when I have all the criteria in the same cell, it sums 3 times in this example:

enter image description here

The expected result in this example would be 4. I want to sum the cell if any one of the criteria is true, but I want it to sum only once even though more than one criteria is true.
What could I do in the formula to make it work like that?

Solution

  • You can use:

    =SUMPRODUCT(B92:B99,REGEXMATCH(A92:A99,"(?i)\b(apple|orange|banana)\b"))

    This formula sums the values in B92:B99 where the corresponding value in A92:A99 contains at least one of the words "apple", "orange" or "banana".