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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.