How to sumifs auto increment date change for a criteria?

Hi all,

I'm new to excel forum and trying to learn excel! Thanks for your help in advance.

I'm trying to do a sumifs based on two criteria, one of the criteria being "date". The formula I have now is this:

=sumifs(actual_cost, category, "Food", date, "6/14/16")

Keep in mind the actual_cost, category and date are name range I created.

I have to do this for 365 days starting 6/14/16, 6/15/16, 6/16/16....and so on.

Is there a way for the dates to automatically increment so I don't have to manually enter for each date?

Thanks for any advice!

There are several things I could think of doing in this situation.

One idea is:
If you do not mind having another column, just put your start date that you want in the top row, and drag down. It will increase the date by one every row, then in your formula you can just do a relative reference to the cells with the dates in them. Finally you can hide the column that has that dates in it so you don't have to look at it.

If you don't want a helper column as mention by "TheMechEngineer" then try this...

=SUMIFS(actual_cost, category, "Food", date, "6/14/16"+ROWS(A$1:A1)-1)

copy down

Hi "TheMechEngineer" and "Teethless Mama" - thank you SO MUCH for your advice!

I'm relatively new to these formula in excel, so bare with me...

Teethless Mama - thank you for your tips - IT WORKED!!!

But I want to understand the formula - if you can explain to me?

=SUMIFS(actual_cost, category, "Food", date, "6/14/16"+ROWS(A$1:A1)-1)

What does +ROW(A$1:A1)-1 mean? I'm having a hard time understanding this part.

Your clarification will be so helpful for me!

THANK YOU AGAIN!!!!

The ROWS(A$1:A1)-1 acts as a counter so as you drag the formula down you get ROWS(A$1:A1)-1, ROWS(A$1:A2)-1, ROWS(A$1:A3)-1 which returns value of 1-1=0, 2-1=1 , 3-1=2 onwards.

This adds 0,1,2 to the date in each row so you get 6/14/16, 6/15/16, 6/16/16 .....