Search code examples
regexgoogle-sheetsgoogle-sheets-formulagoogle-sheets-querysumproduct

How can I SUM values based om multiple criteria where one is a range (list of values)?


I am using Google Sheets, but it looks like nowadays it supports most of Excel functions as well.

I want to get SUM based on multiple criteria. I have found formulas for each criterion, but I have not found any way to combine those criteria.

Example image of what I achieved

SUM of all values (G) where E="X" - SUMIFS(G3:G9;E3:E9;"X")

SUM of all values (G) where F is in B - SUMPRODUCT(SUMIF(F3:F9;B3:B7;G3:G9))

What formula can I use?

SUM of all values (G) where F is in B AND E="X" - ??


Solution

  • Try this:

    =sumproduct(isnumber(match(F3:F9,B3:B7,0)),(E3:E9="X"),G3:G9)
    

    A brief explanation

    isnumber(match(F3:F9,B3:B7,0)) ceates a column of true (1s) and false (0s) values if the value in column F is found in column B.

    (E3:E9="X") does the same thing if column E has the value X

    sumproduct(): and where both the above are true (1s), these get multiplied by the corresponding value in G and the whole column is added up. If either is zero, the corresponding value is zero, of course.

    enter image description here enter image description here