How do I use autofill to change the dates in a COUNTIF formula?

**viper50** · 07-15-2010, 08:30 AM

How do I use autofill to change the dates in a COUNTIF formula? Example:
A1 cell: =COUNTIF(B1:B100, "1/1/2010")
A2 cell: =COUNTIF(B1:B100, "1/2/2010")

I want it to autofill the formula with 1/3/2010, 1/4/2010, etc. when I drag it down, but it just repeats the first 2 dates over and over. Any suggestions? Thanks!

**NBVC** · 07-15-2010, 08:32 AM

One way:

=COUNTIF(B3:B102, ("1/"&ROWS($A$1:$A1)&"/2010")+0)

copied down

or

=COUNTIF(B1:B100,DATE(2010,1,ROWS($A$1:$A1)))

copied down.

Actually the second is better...because it will automatically go to next month after the last day.. the first formula is only good for January....

**viper50** · 07-15-2010, 08:57 AM

Hmmmm, I tried both and neither seemed to work, I was probably doing something wrong...

I got it to work by puttin this in column B and dragging down:
=COUNTIF(C$1:C$100, A1)
=COUNTIF(C$1:C$100, A2)
etc etc etc

With Column A being the dates I wished to count and column C being the data counted.
Thanks!

**NBVC** · 07-15-2010, 09:02 AM

Sorry, I did forget to make the range absolute:

=COUNTIF($B$1:$B$100,DATE(2010,1,ROWS($A$1:$A1)))

this checks that the date in B1:B100 is Jan 1, 2010, as you copy down it should count Jan 2, 2010, etc...

Ignore the first formula.. it is not good.