Solved: SumProduct RowsandColumns

[davew]

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]

This formula can probably be shortened, but it seems to work.
In B3:[VBA]=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))))[/VBA]
in B4:[VBA]=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))))[/VBA]in B5:[VBA]=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))))[/VBA]
all copied across.

[davew]

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