Search code examples
excelexcel-formulagoogle-sheetsformulassumifs

Google sheets sumproduct sumif multiple criteria that does not contain certain keywords summed per keyword and not once for all

I have a sheet that sums certain expenses based on multiple criteria

For example utilities are summed in a following way

=SUMPRODUCT(sumif(A:A,{"*water*","*intern*","*rent*"},C:C))

I have sums like that for many different expenses, now issue is I am trying to have a sum of "other expenses", where I would add everything that is NOT the keywords that I have used previously... I have tried using negation in following manner

=SUMPRODUCT(sumif(A:A,{"<>*Vanduo*","<>*int*","<>*nuoma*"},C:C)) (list of excluded values here isn't full it's just an example)

What I got is everything added for each keyword every time, what I mean is my total expenses were around 11k but what I got after using formula above is ~33k, which implies that it added everything for each keyword every time...

How to do a sumif sumproduct (or some other way) that would exclude keywords, but would do it only once and not per keyword?

P.S I have already solved this problem by having a sum of everything in C column and then subtracting all the sumproduct sumifs that I have, however I would like to compare this value to value with negated keywords to check for errors.

Solution

  • The method you are using is OR, You want AND

    =SUMIFS(C:C,A:A,"<>*Vanduo*",A:A,"<>*int*",A:A,"<>*nuoma*")