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!
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....
Last edited by NBVC; 07-15-2010 at 08:36 AM.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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!
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks