sum multiple cells from a 2d array

[koneil]

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 B25 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.

 

[Domenic]

Try...

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

Hope this helps!

 

[JMB]

This also appears to work:

=SUMPRODUCT(B25*(B11=999)*(A2:A5=100))

 

[JMB]

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)