SumIFS <= date question

[RussellNonBrand]

Hey,
So I have a table with market and month (in date format).

I want one cell to sum all values from months less than or equal to the current month.

So I was going to nest a match function inside of summing an index function, but can't figure out how to get the columns to only be those for months <= the month specified.

Market............1/1/2013............2/1/2013............3/1/2013............4/1/2013
Atlanta...............20....................30....................24........................40
Boston................22....................18....................44........................30

I want to sum everything for Atlanta when the date I input is less than or equal to 3/1/2013. Answer should be 74.

Can someone please help? Thanks!

 

[njimack]

Welcome to the board.

Excel Workbook
	A	B	C	D	E	F	G
1	Market	01/01/2013	01/02/2013	01/03/2013	01/04/2013		01-Mar
2	Atlanta	20	30	24	40		74
3	Boston	22	18	44	30		84
Sheet1

 

[RussellNonBrand]

Thank you... my problem is I need to choose how to auto-update depending on when I choose a market from a list. Ultimately this will populate a dynamic graph.

I had previously written

=SUM(INDEX("Sum Range",MATCH("Atlanta","Name Range",0),0))

(I had to use quotations instead of carats for the ranges)

But this will only sum the entire row. I need to restrict so it will auto-choose the market then sum dates less than or equal to the date specified.

 

[Jonmo1]

Try

=SUMIF(B$1:E$1,"<="&G$1,INDEX(B2:E3,MATCH("Atlanta",A2:A3,0),0))

You can replace "Atlanta" with a cell reference, say $H$1
=SUMIF(B$1:E$1,"<="&G$1,INDEX(B2:E3,MATCH($H$1,A2:A3,0),0))

 

[RussellNonBrand]

Ahh just had it backwards. Thanks a ton!