Dynamic Data

This is easiest accomplished using INDIRECT(). the INDIRECT() function passes an address that you can manipulate to a function. In this case you probably wab tto use the COUNTIF() function to count the number of MM and JJ occurrences in each column, but only for the last 90 days. So we will use INDIRECT to get the address to pass to COUTIF().

As the dates increase to the bottom (assuming you are adding each new day to the bottom of the column), we can use the MAX() function to get the last date. So as your dates are in column A, =MAX(A:A) will give me the last date. Now I am not particularly interested in the last date, I want to know the row number of the last date! To do that I can use the MATCH() function in this case. MATCH() will give me the position of a value in a range. If my range is a complete column then its position is also the row number:
=MATCH(MAX(A:A),A:A)
So now i can construct my address for column B as:
="B"&MATCH(MAX(A:A),A:A)-90&":B"&MATCH(MAX(A:A),A:A)
which would show something like: B23:B113
so for my summary table (if it were on the same sheet) the formula would be:
=COUNTIF("B"&MATCH(MAX(A:A),A:A)-90&":B"&MATCH(MAX(A:A),A:A),"=MM")
to count the occurrences of MM.
As your summary table is on a different sheet, we also need to put in the sheet name, which will make the formula quite long. As we need to do this for 12 columns and for several search terms it will be better to put =MATCH(MAX(A:A),A:A) in a cell somewhere so we can just refer to this cell rather then repeat it all the time (which also slows down Excel a little bit)

So on your summary sheet say that in cell Z3 you have
=MATCH(MAX(DUTIES!A:A),DUTIES!A:A)

then in the summary table you can write:
=COUNTIF("DUTIES!B"&$Z$3-90&":B"&$Z$3,"=MM")

Thanks for the reply (and sorry for the double post!)

Firstly, with regard to the dates, I am not adding a new date every day as the sheet caters for the coming year, so there are dates in column A beyond today. How do I extract todays date from column A on the DUTIES sheet and display it on the SUMMARY sheet? At the moment I have entered =NOW() in a cell on the SUMMARY sheet and =MATCH(SUMMARY!N21,A:A) in a cell on the DUTIES sheet (where N21 is the cell with the =NOW function in it), so that I have a reference for the rest of the formulas. The reference for the cell with the MATCH function in it is on the SUMMARY sheet in O21.

I constructed my formula for column B, (on the DUTIES sheet) as ="B"&MATCH(SUMMARY!N21,A:A)-90&":B"&MATCH(SUMMARY!N21,A:A) and that seems to generate the correct row references. Then I cut and paste it into N23 on the SUMMARY sheet.

On the SUMMARY sheet I have entered =COUNTIF("DUTIES!B"&$O$21-90&":B"&$O$21,"=MM"), where O21 is the cell with todays date reference (Z3 in your example). I copied and pasted from your example above and just substituted my O21 for your Z3, but I keep getting the message "this formula contains an error".

Any ideas?

Yes: in O21 you have a date and it expects a row number. If you already have all the dates filled out and you want it for today then in O21 put the formula
=MATCH(TODAY(),Sheet1!A:A)

Hmmm,OK..

I did as you suggested and put =MATCH(TODAY(),DUTIES!A:A) in cell O23 on the SUMMARY sheet and it now gives a result of 414, which matches todays date on the DUTIES sheet.

But when I put =COUNTIF("DUTIES!B"&$O$23-90&":B"&$O$23,"=MM") into a cell on the SUMMARY sheet, I still get the error message. Does the COUNTIF formula have to be on the same sheet as the O23 it refers to?

Thanks!

ah, yes. So if O23 is on the DUTIES sheet the formula would be:
=COUNTIF("DUTIES!B"&DUTIES!$O$23-90&":B"&DUTIES!$O$23,"=MM")

Basically you are putting a string together consisting of
"DUTIES!B" value in DUTIES!O23-90 ":B" value in DUTIES!O23

So what excel eventually sees as the formula is for instance (if O23 is 480)
=COUNTIF(DUTIES!B390:B480,"=MM")

So if you take away the COUNTIF( and ,"=MM") you should end up with a nice address. That is always an easy way to check to see if the formula gets the right address.

Hi again, and thanks for your persistence.

I took out the COUNTIF and "=MM" as you said and get a result in the target cell of DUTIES!B325:B415 , which is the correct address for counting the last 90 days. But when I add the COUNTIF and "=MM", I still get the error message….

The O23 cell is on the SUMMARY sheet, not the DUTIES sheet.

That is a bit strange.
In a cell below it (or somewere else) try and build the COUNTIF formula from scratch but without the complicated formula inside. Just with pointing the mouse to get the range etc.

once that works, replace the range with the formula to construct the range.

Try to post a sample such that the problem is scaled-down to last 9 days...

Hi guys,
Yes, as I said I'm new at this

I can get the formula to give me a range over the last 90 days by selecting it with the mouse, but I don't seem to be able to write the formula so that it automatically updates from the O23 cell that refers to the current date. I can see that the "O23" and the "O23-90" will give me the right range, but can I ask what the function of the $ and & characters are?