Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using sumproduct | Excel Discussion (Misc queries) | |||
How can I use SumProduct with a horizonontal and vertical array? | Excel Discussion (Misc queries) | |||
HORISONTAL AND VERTICAL AREA IN SUMPRODUCT | Excel Worksheet Functions | |||
sumproduct w/horizontal range not working | Excel Worksheet Functions |