COUNTIF formula

[enthral]

Good morning,

I've been given a task at work and am having a little bit of trouble with copying a countif formula.

The formula starts off as =COUNTIF($D$2:$D$885,"31/03/2017")

When I copy the formula I want the date associated with the formula to increase by one day each time. So the next forumla would be =COUNTIF($D$2:$D$885,"01/04/2017"), =COUNTIF($D$2:$D$885,"02/04/2017") etc etc.

Is it possible to do this?

Many thanks

 

[FormR]

Hi, you could try like this:

=COUNTIF($D$2:$D$885,DATE(2017,4,ROWS(A$1:A1)-1))

For robustness change the A1 references in red to refer to the same cell where you first enter the formula before copying down.

 

[RoryA]

Welcome to the forum.

Why don't you put the dates into an adjacent column (which you can fill down) and then refer to that in the COUNTIF formula? It will make it much more obvious what each row is doing.

 

[Special-K99]

Copying it where? Downwards?

Try

=COUNTIF($D$2:$D$885,DATEVALUE("31/03/2017")+(ROWS($1:1)-1))

This assumes your dates are actual dates and not text

 

[enthral]

Thank you so much for giving me a hand. All of your solutions worked, and I have decided to use Special's formula.