Dragging SUMPRODUCT formulas downward is not working properly

Hello once again!

We still need help on a solution on how to drag a few of our formulas down ... I've attached a worksheet that has only the tables with the problem. We manually filled in the 2013 formulas. When they are dragged down to fill 2014-2020, the references within the formula change. The only part of the formula that we wan t to change is the "DATE".

We thought we had this problem solved by using a suggestion that involved the INDIRECT command. It caused problems once files were opened and closed ...

I hope someone can come up with a solution. Thank you for helping once again!!

I believe this is what most are thinking of.

So you guys are saying that I should use COLUMN H to represent mmyyyy ....

So if cell H11 had 092013 stored in it as mmyyyy, then I could write the formula as:

=SUMPRODUCT((TEXT($KW$2:$KW27,"mmyyyy")="H11")*$KX$2:$KX27)


I'll try it out! Thanks!!

Guys, I tried both and neither seem to work. I formatted the new date column with mmyyyy, so that's not the problem.

I tried it in a cell that pulled a value in from the $KX$2:$KX27 range .... when I used the H11 in place of the 092013 it didn't pull the value that it did previously.

OK, we got it to work if the reference date cells were typed in as TEXT and not mmyyyy. Is that weird?? But hey, it's working!

Thank you for the help!! If there are any other suggestions, please offer them!!

Cheers!!

TMS, that's a neat way to get it done! Thank you!!

I really appreciate the help that you all offer. This Forum is the best I've ever used. I wish I could add some value. All I do is ask questions!