Trying to get the date (Within COUNTIF Formula) to increment when using auto-fill feature.

Hi All,

My issue at hand is that I am unable to get the auto-fill feature to work on the date, within the below formula's:

=COUNTIF('Response Times'!$D:$D,"9/1/2013")
=IFERROR(AVERAGEIFS('Response Times'!$A:$A,'Response Times'!$D:$D,"9/1/2013"),0)

Within the attached workbook, there is a chart, in the 'September' sheet, and starting with cell C4, I used the autofill feature to drag the formula to cell AG4. But...the date in the formula of C4 (9/1/2013), did not increment. I had to manually enter the correct date in each additional cell.

So the question is...How do I write the formula to get the date to increment, when utilizing the autofill feature?

Note: The purpose of Row 4 'Number of Alerts', is to count the # of alerts on a per day basis, via the dates on the 'Response Times' sheet, in Column D.

Note: The purpose of Row 5 'ART /Day (secs), is to calculate the average of the response times on a per day basis, in seconds. Meaning: The formula in C5 is to look at the times in Column A of sheet 'Response Times', and if any of those times occurred on the date specified (Within the formula), it will then calculate the average of those times.

Any help is greatly appreciated!

Thanks,
Carmstrong227

First, change the formula in D1 of the Response Times sheet to this:

Formula:

Please Login or Register  to view this content.

Format the cell as a date, then copy down.

Then you can change the formula in C4 of the September sheet to this:

Formula:

Please Login or Register  to view this content.

and in C5 to this:

Formula:

Please Login or Register  to view this content.

then copy both across to column AF (only 30 days in September - if you copy to column AG then that would be treated as 1st October). Then change the formulae in AH4 to this:

Formula:

Please Login or Register  to view this content.

and similarly for AH5. For the October sheet you will need to change the 9 in the DATE functions to 10, or you could just put the month number in a blank cell somewhere on the sheet (e.g. AJ1) and refer to that cell in the DATE functions - use $AJ$1.

Hope this helps.

Pete

Wow!!! I love this site/forum!! And that goes without saying, I love you Pete...lol!

Your suggestions worked absolutely perfect. And not only that, you went out of your way to fix additional discrepancies, and even provided insight on what I should do with the 'October' sheet.

I feel like I should be paying for this

THANKS!!!!