How do I sum a column based on multiple sets of OR criteria?

Question

I have a table with a target sum column (ColA) and three columns to filter based on criteria. I've been using SUMIFS to calculate the sum of ColA based on the criteria columns Cols B through D however I notice I'm not doing this correctly. One thing to note is that the criteria columns have varying amounts of criteria each (e.g. column B has more OR conditions than C)

My current formula is:

SUM(SUMIFS(table[ColA], table[colB], {"val1", "val2", "val3"}, table[colC], {"val1", "val2"}, table[colD], {"val1", "val2", "val3", "val4"}))

The result of this formula is incorrect after manually checking the sum in the underlying table. Any advice on how to sum a column based on a set of multiple OR values?

Answer 1

You will need to do a SUMPRODUCT which will get long:

=SUMPRODUCT(table[ColA]*
  ((table[colB]="val1")+(table[colB]="val2")+(table[colB]="val3")>0)*
  ((table[colC]="val1")+(table[colC]="val2")>0)*
  ((table[colD]="val1")+(table[colD]="val2")+(table[colD]="val3")+(table[colD]="val4")>0))

To understand, the * is the equivalent to AND, the + is equivalent to OR

Answer 2

Let's say your table is called Table1, starts in A1 with headers, and is 4 columns wide. In a cell somewhere to the right, let's say G2, you could enter this formula:

=AND(OR(B2={"val1","val2","val3"}),OR(C2={"val1","val2"}),OR(D2={"val1","val2","val3","val4"}))

leaving G1 blank, then your sum formula is just:

=DSUM(Table1[#All],1,G1:G2)

You could also break the criteria formula up into three cells, one for each column, using:

=OR(B2={"val1","val2","val3"})
=OR(C2={"val1","val2"})
=OR(D2={"val1","val2","val3","val4"})

in G2:I2 (either leave G1:I1 blank, or give them labels that do not match any of your table headers), then use:

=DSUM(Table1[#All],1,G1:I2)

Answer 3

You could try to use MATCH() nested in SUMPRODUCT():

Formula in F1:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B6,{"Val1","Val2","Val3"},0)+MATCH(C2:C6,{"Val1","Val2"},0)+MATCH(D2:D6,{"Val1","Val2","Val3","Val4"},0))*A2:A6)