# SumIFS <= date question

#### RussellNonBrand

##### New Member
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.

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Welcome to the board.

Excel Workbook
ABCDEFG
1Market01/01/201301/02/201301/03/201301/04/201301-Mar
2Atlanta2030244074
3Boston2218443084
Sheet1

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
Code:
``=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.

Last edited:
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))

Ahh just had it backwards. Thanks a ton!

Replies
7
Views
452
Replies
0
Views
196
Replies
5
Views
539
Replies
7
Views
359
Replies
2
Views
222

1,203,059
Messages
6,053,292
Members
444,650
Latest member
bookendinSA

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back