Silly question about using dates

Z

Zerosumgame

I have a multi-page worksheet, sorted out by date and type of work done. I am
looking for a reasonable way to COUNT all the entries in certain months.
Every function i try errors out. And combining MONTH with COUNTIF does not
seem to work. i do not want to SUM them, just to COUNT them.
 
J

JMB

Don't discount the sum functions for counting

=SUMPRODUCT(--(MONTH(A1:A5)=2))

will count all of the dates in A1:A5 that are February (ie equal to 2).

You could also use the regular sum function, but it would have to comitted
w/Control+Shift+Enter

=SUM(--(MONTH(A1:A5)=2))
 
Z

zerosumgame

I should add that it seems named ranges do not work in the MONTH command. Am
I wrong on that or just doing it wrong?
 
G

Gilles Desjardins

Let me take a wild guess: Try COUNTA(X?:X?)

See if that works. Replacethe X and ? by your own reference

Gilles
 
D

daddylonglegs

There's no reason that you can't use named ranges like

=SUMPRODUCT(--(MONTH(daterange)=2))

.....but you might need to be careful with this formula when counting
January, empty cells will be counted also, so use

=SUMPRODUCT(--(MONTH(daterange)=1),--ISNUMBER(daterange))
 
R

Ron Rosenfeld

I have a multi-page worksheet, sorted out by date and type of work done. I am
looking for a reasonable way to COUNT all the entries in certain months.
Every function i try errors out. And combining MONTH with COUNTIF does not
seem to work. i do not want to SUM them, just to COUNT them.
Click to expand...


=COUNTIF(rng,">="&DATE(2006,1,1)) - COUNTIF(rng,">"&DATE(2006,1,31)

will give you the count for the month of January 2006

Perhaps you can adapt this to your issue?

If you want the count for ALL january's , then SUMPRODUCT will work.
--ron
 
Top