Using SUMPRODUCT and INDIRECT together

Hi

I have a spreadsheet that has a rolling calendar that automatically updates to show todays date and the last 365 days when opened using a macro
The calendar dates starts at cell A18 through to A383

I have the below formula looking for "Yes" in column F over the last 182 days to calculate a trigger point, but the cell reference changes everyday by one, so if I open the spreadsheet tomorrow the cell refeence will start A19:A384 and F19:F384 which I dont want it to do.

=SUMPRODUCT(--(A18:A383>=TODAY()-182),--(F18:F383="YES"))

I tried the $ with the cell references but this didnt work and I sumbled across the INDIRECT function and thought this may be a solution ?

Would INDIRECT stop the cell reference from moving and if so how would I combine it with my above formula ?

Many thanks for your help in advance
CAB

You could try:

Please Login or Register  to view this content.

But:

Please Login or Register  to view this content.

Should also work. Maybe the macro rolling the rows might effect it. Hopefully the indirect will work for you.

thank you I will give that a go