Change Date Range when dragging SUMIFS equation

I need to update my spreadsheet yearly for new work orders and changing each cell is quite tedious. The following is my equation:

=SUMIFS('NWMCC Quantities'!$AK$6:$AK$6734,'NWMCC Quantities'!$O$6:$O$6734,"Light",'NWMCC Quantities'!$A$6:$A$6734,">=01/01/2016",'NWMCC Quantities'!$A$6:$A$6734,"<02/01/2016")

when dragging the equation I would like it to become:

=SUMIFS('NWMCC Quantities'!$AK$6:$AK$6734,'NWMCC Quantities'!$O$6:$O$6734,"Light",'NWMCC Quantities'!$A$6:$A$6734,">=02/01/2016",'NWMCC Quantities'!$A$6:$A$6734,"<03/01/2016")

and so on.

I have tried updating the second cell to give it the pattern, but it will just repeat the same two ranges over and over. I have also tried putting the dates into separate cells on another sheet and using the Cell ID in the equation instead of the date, but then the equation will just compute a zero.

Thank you.

=SUMIFS('NWMCC Quantities'!$AK$6:$AK$6734,'NWMCC Quantities'!$O$6:$O$6734,"Light",'NWMCC Quantities'!$A$6:$A$6734,">="&Code!A1,'NWMCC Quantities'!$A$6:$A$6734,"<"&Code!B1)

Is my new code. Using sheet "code" to hide the date cells as this is a dashboard for presentations. If my dates on the code sheet are reading as 1/1/2016 and 2/1/2016 would it make the difference?

Just made the update in the formatting to match the original equation and it still zero outs the equation

Just updated them to mm/dd/yyyy format to match the numbers in the original equation.

Working on the assumption that the original formula works with hard coded dates like this ">=01/01/2016"
Then the problem is more likely that the dates were not correctly entered as dates on the Code sheet, A1 and B1

What do these return
=ISNUMBER(Code!A1)
=ISNUMBER(Code!B1)

Originally Posted by Jonmo1

Working on the assumption that the original formula works with hard coded dates like this ">=01/01/2016"
Then the problem is more likely that the dates were not correctly entered as dates on the Code sheet, A1 and B1

What do these return
=ISNUMBER(Code!A1)
=ISNUMBER(Code!B1)

The coding does work with hard coded dates in the formula. Both of these equations return a True Statement

Maybe as Tony eluded to in Post #6
The dates you put in A1 and B1 are not the same as the ones you hard coded in the formula..

Maybe you entered 02/01/2016 expecting that to be February 1st, but Excel read it as January 2nd (or vice versa)

Format those 2 cells as
mmm dd yyyy

Do they show the date you expected?

With that formatting they show as Jan/01/2016 and still zeroed out the equation. With the mm dd yyyy they show the same as in the hard code

With that formatting they show as Jan/01/2016

And the other? There were 2 dates in question.

Can you give specific answer,
A1 formatted as mmm dd yyyy shows as ??
B1 formatted as mmm dd yyyy shows as ??

Just got it to work. Made an embarrassing rookie mistake while going through this. Had the wrong code date going into the range, so the SUMIF equation was returning everything as false. Thank you both of you for your help. In getting this setup. It will make my life much easier for new contracts. Thank you again.

You're welcome.