I get the wrong answer if there is more than one column that has 999 (don't
know if that is an issue for the OP or not)

"Domenic" wrote:

Try...

=SUMIF($A$2:$A$5,100,INDEX($B$2:$D$5,0,MATCH(999,$ B$1:$D$1,0)))

Hope this helps!

In article ,
koneil wrote:

I am sure there is a straightforward way to do this, but somehow I can't
seem to get it to work. I want to sum multiple cells in a 2d array, based
off the x and y values in a given column. As a simplified example, use the
data:
A B C D

1 568 783 999 9091
2 200 12 14 12
3 100 11 9 0
4 100 0 1 4
5 390 2 5.5 6

My problem, then, is that I wish to have a formula which sums all cells in
the range B2:D5 for which column "A" has the number 100 and row 1 has the
number 999 (that is, the answer here should be 10). Of course the real
spreadsheet is much larger than this, but this example should suffice

thanks.