Trying to add cells with multiple criteria that are less than or equal to a date

[rdelbalso]

I am not an Excel expert and am having troubles with a spreadsheet for work. I am trying to add multiple cells that are in columns that are less than or equal to a given date, but only in the row that matches an index criteria. For example

Index 5/1/14 6/1/14 7/1/14 8/1/14
SHC $50 $500 $5000 $50000
SOS $100 $1000 $10000 $100000
SEI $200 $2000 $20000 $200000
SBS $300 $3000 $30000 $300000

Sum all values for SOS with a date less than or equal to 7/1/14.

My spreadsheet is a bit more complicated than this, but this is the gist of the problem. I was trying to figure out how to use an array with Index Match, but I can't get that to show all values less than or equal to the date, only the exact match.

Any ideas?
Thanks in advance for helping out a confused user.
Ryan

[martindwilson]

try with your data in a1:k5 and date to search for in a1
=SUMPRODUCT(($A$2:$A$5="shc")*($B$2:INDEX($B$2:$K$5,0,MATCH($A$1,$B$1:$K$1,0))))

	A	B	C	D	E	F	G	H	I	J	K	L
1	06/01/2014	05/01/2014	06/01/2014	07/01/2014	08/01/2014
2	SHC	50	500	5000	50000
3	SOS	100	1000	10000	100000
4	SEI	200	2000	20000	200000
5	SBS	300	3000	30000	300000
6
7
8
9	SHC	550	formula in b9 is >>> filled down	=SUMPRODUCT(($A$2:$A$5=A9)*($B$2:INDEX($B$2:$K$5,0,MATCH($A$1,$B$1:$K$1,0))))
10	SOS	1100
11	SEI	2200
12	SBS	3300
13
14

[Glenn Kennedy]

Like this...???

[rdelbalso]

Thank you Glenn. That does it. I tried using sumproduct before, but for some reason I couldn't get it to work. I must have had the syntax wrong. This is great. Ahh, I just don't think in Excel and have been wracking my brain trying to get it and the answer was so simple. Is there a way to do this with index, match?

Martindwilson, I tried your index match solution and just got back an #N/A.
Thanks all.
Ryan

[martindwilson]

well it works ! otherwise it wouldnt show the results i posted, but glens is shorter
t

[Glenn Kennedy]

Yeas, but it would be a bit of a nightmare. By the way, Martin's formula works perfectly. i've added it onto the sheet I posted earlier...

[rdelbalso]

Thanks Glenn and Martin. Now I see how Martin's works. I don't know why I couldn't get it to work.

So I am curious on Martin's solution. I don't understand how that is getting all the values that have a date less than or equal to the selected value. To my eye it seems like it would only get the exact match, but it doesn't. Why?

This is extremely helpful.

[martindwilson]

the index creates the range so for 7/1/2014
$B$2:INDEX($B$2:$K$5,0,MATCH($A$1,$B$1:$K$1,0)
$B$2:INDEX($B$2:$K$5,0,3)
$B$2:INDEX($D$2:$D$5,0)

resolves to b2:d5

[rdelbalso]

Ah. I see. So that only works if the dates are listed in ascending order. For my current case that will probably always be true, but for some other cases I have that won't always be true so I think I will stick with Glenn's solution. Thanks Martin. It really helps to understand how these work so I can use them appropriately in the future.
Ryan

[Glenn Kennedy]

A happy man!! If that's it, can you please mark the thread as solved by selecting Thread Tools from the menu link above and mark this thread as SOLVED, and preferably also click the add reputation button at the foot of the post(s) of all members who helped you reach a solution.

[rdelbalso]

Of course. Done.