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?

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" <(E-Mail Removed)> wrote in message
news:2D70097B-4E4D-4773-B75D-(E-Mail Removed)...
>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?

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 here:
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