sum multiple cells from a 2d array
 

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.

sum multiple cells from a 2d array
 

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.

sum multiple cells from a 2d array
 

This also appears to work:

=SUMPRODUCT(B2:D5*(B1:D1=999)*(A2:A5=100))


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

sum multiple cells from a 2d array
 

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.