How to count days in excel
 

I am trying to use countif to count the number of times within a month that
my fire department has a call, meeting, truck check. I have the dates in
mm/dd/yy format. I figure that all I would have to do is use =COUNTIF
(A1:A25, 11/*/09) and it would work. But that is not the case. when I add
more calls/meetings/truck checks it does not add the new one to the total.
And yes I did change A25 to A26 so that it new to look there also. But the
total won't change. What am I doing wrong?

How to count days in excel
 

One way...

=SUMPRODUCT(--(MONTH(A1:A25)=n))

Where n = the month number from 1 to 12.

Note that empty cells will evaluate as month number 1 (Jan). To account for
that:

=SUMPRODUCT(--(A1:A25<""),--(MONTH(A1:A25)=n))

--
Biff
Microsoft Excel MVP


"firemedic1203" wrote in message
...
I am trying to use countif to count the number of times within a month that
my fire department has a call, meeting, truck check. I have the dates in
mm/dd/yy format. I figure that all I would have to do is use =COUNTIF
(A1:A25, 11/*/09) and it would work. But that is not the case. when I
add
more calls/meetings/truck checks it does not add the new one to the total.
And yes I did change A25 to A26 so that it new to look there also. But
the
total won't change. What am I doing wrong?

How to count days in excel
 

And if you really wanted to count the number of items in November of 2009:

=sumproduct(--(text(a1:a10,"yyyymm")="200911"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

=========
You could also count the dates larger than Oct 31, 2009 and subtract the dates
larger or equal to Dec 1, 2009.

=countif(a:a,""&date(2009,10,31)) - countif(a:a,"="&date(2009,12,1))


firemedic1203 wrote:

I am trying to use countif to count the number of times within a month that
my fire department has a call, meeting, truck check. I have the dates in
mm/dd/yy format. I figure that all I would have to do is use =COUNTIF
(A1:A25, 11/*/09) and it would work. But that is not the case. when I add
more calls/meetings/truck checks it does not add the new one to the total.
And yes I did change A25 to A26 so that it new to look there also. But the
total won't change. What am I doing wrong?

--

Dave Peterson