Full Version: COUNTIF Query
Kell
Hi,

I have got a column containing dates in the format dd/mm/yyyy. I want to be able to do a count of these dates for each month i.e. 12 dates in September, 3 in October etc. so that I can count how many entries were made in September, October etc.

I can get it to count just the days:

=COUNTIF(A1:A50,"01/09/2002")

but I do not know how I would count how many entries are made for that month, not day?

Any help will be appreciated.

Thanks

Kel
MarkM
OK, ever heard of array formula's?? Well here goes:

type the following formula but don't press enter:

=count(if(month(a1:a50)=9,a1:a50))

Now hold down Ctrl + Shift and now hit enter. The formula should now have "{" type brackets around it to look like this:

{=count(if(month(a1:a50)=9,a1:a50))}

This should work. Bare in mind that if you need to change the formula for whatever reason, you need to do the whole Ctrl + Shift + Enter thing again.

Look in help if you need to understand how this works. One thing you should remember is that if there are any blank cells in a range of dates Excel treats them as 01/01/1900, so if you are looking at dates occuring in January, and you have blank cells in the range, the formula will over-count.

HTH
kountry
Good answer and thanks for the advice on the pitfalls of testing a whole batch of dates.
Couldn't you also add an if statement to the Countif function which excludes any dates with Year(Date)=1900? Would that work?

Cheers
MarkM
Yes, of course, I was just leaving that up to the poster to work out for herself!