Duplication of Counts

[BuckeyeJohn21]

How can I prevent excel from counting entries twice when totaling date
ranges. I am using the following formula:

January Total Cell
=COUNTIF(F2:F507,"<="&DATE(2010,2,1))-COUNTIF(F2:F507,"=<"&DATE(2010,1,1))
February Total Cell
=COUNTIF(F2:F507,"<="&DATE(2010,3,1))-COUNTIF(F2:F507,"=<"&DATE(2010,2,1))

and so on. It duplicates the totals from previous months into the future
months. Frustrating!


Answer

=COUNTIF(A8:A20,">"&DATE(2009,1,31))-COUNTIF(A8:A20,">="&DATE(2009,3,1))
Adjust your range to suit.

 

[Mike H]

How can I prevent excel from counting entries twice when totaling date

ranges. I am using the following formula:

There is an error in your formula if your trying to count January, try this

=COUNTIF(F2:F507,"<"&DATE(2010,2,1))-COUNTIF(F2:F507,"<"&DATE(2010,1,1))

Mike

 

[Dave Peterson]

Another way to count the number of dates in January of 2010:
=sumproduct(--(text(f2:f507,"yyyymm")="201001")

You didn't ask, but if you wanted to count the number of dates in January of any
year:
=sumproduct(--(isnumber(f2:f507)),--(month(f2:f507)=1))

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