Sumproduct and horizontal lookup
 

I have the following spreadsheet:
A B C D
1 Jan Feb
2 blue 50 30
3 red 125 544
4 yellow 40 44
5 blue 30 122
6 red 54 60
7 red 100 80

How do I use sumproduct with a lookup in the colums. For ex. I want to know
how much I have in the blue for Jan and Feb. I don't won't to use pivot
tables.

Thanks Gingit.

Sumproduct and horizontal lookup
 

I guess you have a bigger range in reality but for your example perhaps
to give you a sum for blue items in jan column

=SUMPRODUCT(--(A2:A7="Blue"),INDEX(B2:C7,0,MATCH("jan",B1:C1,0)) )


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=551237

Sumproduct and horizontal lookup
 

Hi daddylonglegs,

This is only a sample, my spreadsheet is much more complex. But your
formula didn't work :( I should clarify that my spreadsheet starts at col A
if that matters.

Thanks,

"daddylonglegs" wrote:


I guess you have a bigger range in reality but for your example perhaps
to give you a sum for blue items in jan column

=SUMPRODUCT(--(A2:A7="Blue"),INDEX(B2:C7,0,MATCH("jan",B1:C1,0)) )


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=551237

Sumproduct and horizontal lookup
 

Looks like it worked afterall

THANKS :-)

"Gingit" wrote:

Hi daddylonglegs,

This is only a sample, my spreadsheet is much more complex. But your
formula didn't work :( I should clarify that my spreadsheet starts at col A
if that matters.

Thanks,

"daddylonglegs" wrote:


I guess you have a bigger range in reality but for your example perhaps
to give you a sum for blue items in jan column

=SUMPRODUCT(--(A2:A7="Blue"),INDEX(B2:C7,0,MATCH("jan",B1:C1,0)) )


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=551237