Use two list of criteria in sumproduct

Question

I'd like to analyze the following table by using two lists of criteria

I'd like to sum column C in case that A contains an element from List 1 and B contains an element from List 2. I could think of using the formula:

=SUMPRODUCT((A:A=List 1) * (B:B=List 2) * (C:C))

My formula yields 2 (a,x,1 + b,y,1), but I'd like to get 3 instead (a,x,1 + b,y,1 + a,y,1).

Can anyone help me?

Answer 1

Something like this should work:

=SUM(SUMIFS(C:C,A:A,{"a","b"},B:B,"x"),SUMIFS(C:C,A:A,{"a","b"},B:B,"y"))

There might be more compact array formulas you could write, but if there are only two variables in each list then this isn't too bad.

Answer 2

If you're okay with using an array formula instead of a sumproduct formula (I find them easier to read but they do carry the risks of being mis-entered when they are edited), try this:

=SUM(IF(ISERROR(MATCH(B1:B6,G2:G3,0)),"",(IF(ISERROR(MATCH(A1:A6,F2:F3,0)),"",C1:C6))))

This works by creating a new array of column A, where rows which match List1 show the value in column C, and rows which do not match List1 show "". This new array is then used to create a new array of column B, where rows which match List2 show the value from our 'new column A-array' (which includes values in column C, as well as some ""), and rows which do not match list2 show "". As the result is an array, it must be summed to provide the ending single answer.

As this is an array formula, when you enter it you will need to complete (every time you edit the cell) by pressing:

CNTRL + SHIFT + ENTER

rather than just

ENTER

Note that while you can instead select all of columns A / B in the index, that is not generally advisable as (in my understanding) an array formula will search all of a column, including the area outside of the used range. This greatly reduces performance, so for your purposes you may have to use one of the other methods of future-proofing (such as indexing an arbitrarily large "A1:A500", or creating some form of indirect formula which incorporates a search for the last row with data in it).

Answer 3

Try this formula in C1:

=COUNTIF(F:F,A1)+COUNTIF(G:G,B1)

All values = 2 meet your AND criteria (in List1 AND in List2)