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

Question

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.

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" - ??

Answer 1

=SUMPRODUCT(QUERY(E2:G, "where E='X' and F='B'", 0))

---

=SUMPRODUCT(QUERY(E2:G, "where not F matches 'F|G|H|I' and E='X' or F='B'", 0))

Answer 2

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.