counting months in a a date column with countif

[kevinherring]

i have a column of dates in the format dd/mm/yyyy

i want to count the number of entries for each month, but how do i ge
the month out of the date?

=COUNTIF(A5:A21, 3) (where 3 is march) doesnt work as countif i
looking at the serial number of the date, not the month

what i want is something like


=COUNTIF(month(A5:A21), 3)

but of course that doesnt work with a range....

thanks for any help
kevi

 

[Bob Umlas]

=SUMPRODUCT(1*(MONTH(A5:A21)=3))

Bob Umlas
Excel MVP

 

[Don Guillett]

if dates are formatted as dates.
=sumproduct((month(a5:a21=3)*1)

 

[Don Guillett]

oops. forgot a )

=sumproduct((month(a5:a21=3)*1)

=sumproduct((month(a5:a21)=3)*1)


--
Don Guillett
SalesAid Software
(e-mail address removed)

if dates are formatted as dates.
=sumproduct((month(a5:a21=3)*1)

 

[Ron Rosenfeld]

i have a column of dates in the format dd/mm/yyyy

i want to count the number of entries for each month, but how do i get
the month out of the date?

=COUNTIF(A5:A21, 3) (where 3 is march) doesnt work as countif is
looking at the serial number of the date, not the month

what i want is something like


=COUNTIF(month(A5:A21), 3)

but of course that doesnt work with a range....

thanks for any help
kevin

If you want to count the number of entries in a particular month & year, then

=COUNTIF(rng, ">="&DATE(2003,2,1)) - COUNTIF(rng,">"&DATE(2003,2,28)
for Feb 2003. You can substitute a cell containing the Date range of the DATE
function.


If you want to count the number of entries for a particular month, irrespective
of the year, then one way is to set up a "helper column". In the first row put
the formula =cell where cell is the location of the first DATE in rng.
Copy/drag it down. Then you can use a formula referencing that column, of the
type: =COUNTIF(helpercolumn,2) for a February count.



--ron