PDA

View Full Version : Solved: SumProduct RowsandColumns



davew
10-16-2012, 04:52 AM
Hi

I'm wanting to use the sumproduct function to pull back values based on the value of two columns and two rows.

I've attached an example to illustrate what I mean.

It would be great if you could take a look as any help would be very much appreciated.

Thanks

davew

p45cal
10-16-2012, 05:42 AM
This formula can probably be shortened, but it seems to work.
In B3:=SUMPRODUCT(--ISNUMBER(MATCH($A$19:$A$30,Tables!$A$2:$A$5,0)),INDEX($B$19:$K$30,,SUMPRODU CT(($B$17:$K$17 & $B$18:$K$18=B$1&B$2)*(COLUMN($B$17:$K$17)-MIN(COLUMN($B$17:$K$17))+1))))
in B4:=SUMPRODUCT(--ISNUMBER(MATCH($A$19:$A$30,Tables!$B$2:$B$5,0)),INDEX($B$19:$K$30,,SUMPRODU CT(($B$17:$K$17 & $B$18:$K$18=B$1&B$2)*(COLUMN($B$17:$K$17)-MIN(COLUMN($B$17:$K$17))+1))))in B5:=SUMPRODUCT(--ISNUMBER(MATCH($A$19:$A$30,Tables!$C$2:$C$6,0)),INDEX($B$19:$K$30,,SUMPRODU CT(($B$17:$K$17 & $B$18:$K$18=B$1&B$2)*(COLUMN($B$17:$K$17)-MIN(COLUMN($B$17:$K$17))+1))))
all copied across.

davew
10-18-2012, 12:12 PM
Thanks very much p45cal. This works great. It is a lengthy formula but I'm nonetheless impressed.

Apologies for taking so long to respond. My response should have matched the speed of your original reply.

Thanks again :bow: