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.
--
David Biddulph


"BuckeyeJohn21" wrote in message
...
I have a workbook with two sheets. On the first sheet are a series of
dates.
On the second tab, I am trying to total the number of entries within
specific months using date ranges from the first sheet. I can't for the
life
of me figure out what formula to use to do this. Can anyone help. An
example of the data I am trying to formulate would be a count of claims
1/31/10 but < 3/1/10. Please help.

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

"BuckeyeJohn21" wrote:

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.
--
David Biddulph


"BuckeyeJohn21" wrote in message
...
I have a workbook with two sheets. On the first sheet are a series of
dates.
On the second tab, I am trying to total the number of entries within
specific months using date ranges from the first sheet. I can't for the
life
of me figure out what formula to use to do this. Can anyone help. An
example of the data I am trying to formulate would be a count of claims
1/31/10 but < 3/1/10. Please help.

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))
--
David Biddulph

"BuckeyeJohn21" wrote in message
...
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.
--
David Biddulph


"BuckeyeJohn21" wrote in message
...
I have a workbook with two sheets. On the first sheet are a series of
dates.
On the second tab, I am trying to total the number of entries within
specific months using date ranges from the first sheet. I can't for the
life
of me figure out what formula to use to do this. Can anyone help. An
example of the data I am trying to formulate would be a count of claims
1/31/10 but < 3/1/10. Please help.

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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

BuckeyeJohn21 wrote:

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.
--
David Biddulph

"BuckeyeJohn21" wrote in message
...
I have a workbook with two sheets. On the first sheet are a series of
dates.
On the second tab, I am trying to total the number of entries within
specific months using date ranges from the first sheet. I can't for the
life
of me figure out what formula to use to do this. Can anyone help. An
example of the data I am trying to formulate would be a count of claims
1/31/10 but < 3/1/10. Please help.



--

Dave Peterson