Summing Array Formulas with Cell References

All,

Just looking for a bit of help -- I'm basically trying to take an array of data with two conditions and trying to use a SUMIFS allowing multiple values per condition. I'm not sure whether it's possible or not to start, and ultimately if it IS possible I'd like to be able to reference a cell in order to calculate the formula. For now, an example set of data:


Condition A Condition B Dollars
A D 20
A E 10
B D 30
C E 20
C D 20

For my purposes, I'd like to find the sum of any rows that are Condition A = (A OR B) and Condition B = (D OR E). Looking at the data, that should equal 60. I'm ultimately working toward a large data set with four criteria, so I'd like to work out one formula that can take the input of other cells to calculate the criteria requirements. My best attempt so far:

=SUM(SUMPRODUCT(($A$5:$A$9={"A","B"})*($B$5:$B$9={"D","E"})*$C$5:$C$9 = 20[/B]

This formula attempt doesn't work because Excel looks for combinations of A+D and B+E, so A+E and B+D (rows 2 and 3 respectively) are excluded. It looks like those two two-element arrays are linked and combined in evaluating the formula -- is there a way to "unlink" them?

Once I find a formula that works for this example case, I'd like to be able to use indirect references to calculate the value. For example, I'd like to have a row in a different sheet with the following information

Condition A Condition B Value
A,B (input) D,E (input) 60 (calculated as value of all combinations of specified criteria)

Any thoughts on how to get the original formula to work, or the type of syntax I might be able to use for indirect referencing?

Thanks much!
An Analyst

So I managed to get the formula working...

=SUMPRODUCT(--ISNUMBER(MATCH($A$5:$A$9,{"A","B"},0)),--ISNUMBER(MATCH($B$5:$B$9,{"D","E"},0)),$C$5:$C$9)

Now the question is -- how can I treat {"A","B"} or {"D","E"} as an input from another cell rather than being hardcoded into the formula? I've tried indirect but it hasn't worked, any thoughts?

Thanks,
An Analyst

Try this:

=SUMPRODUCT(--((A1:A5="A")+(A1:A5="B")),--((B1:B5="D")+(B1:B5="E")),C1:C5)