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

 

[David Biddulph]

Easiest to keep the discussion in the same thread.
In half of the formula you have used the correct symbol for less than or
equals, that is <=
In the other half you have incorrectly written the symbol as =<
Correct that error, and it will work.

Did you, however, intend to include 1st February in your January count,
instead of the February count, because that's what you've done? You may
want to look again at when you use less than or equal and when you use less
than. Think about what each half of the formula is doing, and how it treats
values on the limit.

You originally said you wanted >31 Jan 10 and < 1 Mar 10
I said
=COUNTIF(A8:A20,">"&DATE(2009,1,31))-COUNTIF(A8:A20,">="&DATE(2009,3,1))
but if I get the year right it is
=COUNTIF(A8:A20,">"&DATE(2010,1,31))-COUNTIF(A8:A20,">="&DATE(2010,3,1))

If it is clearer to you to talk of values > 1 Feb 10 and < 1 Mar 10, then
you could change my formula to
=COUNTIF(A8:A20,">="&DATE(2010,2,1))-COUNTIF(A8:A20,">="&DATE(2010,3,1))

 

[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