I need to calulate how many cells in one column have a date that are between
a range of dates for example 1/1/2005 through 12/31/2005.

Thanks.

=countif(a1:a10,"="&date(2005,1,1))-countif(a1:a10,""&date(2005,12,31))

KC wrote:

I need to calulate how many cells in one column have a date that are between
a range of dates for example 1/1/2005 through 12/31/2005.

Thanks.

--

Dave Peterson

I'm a big fan of array formulas... hopefully you are familiar with
them. Below is the formula I used:

=SUM(IF(A2:A778=D3,IF(A2:A778<=D4,1,0),0))

A2:A778 -- was my range of dates
D3 -- cell with value of 1/1/2005
D4 -- cell with value of 12/31/2005

if you use this formula, after typing it, instead of just hitting enter
hit, ctrl-shift-enter... this makes it an array formula and it will put
{} around the formula.



KC wrote:
I need to calulate how many cells in one column have a date that are between
a range of dates for example 1/1/2005 through 12/31/2005.

Thanks.

Try something like this:

B1: 1/1/2005 (start date)
B2: 12/31/2005 (end date)
B3: =SUMPRODUCT((A1:A1000=B1)*(A1:A1000<=B2))

OR...to count all dates witin one year:
B3: =SUMPRODUCT(--(YEAR(A1:A1000)=2005))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"KC" wrote:

I need to calulate how many cells in one column have a date that are between
a range of dates for example 1/1/2005 through 12/31/2005.

Thanks.

If it's a whole year like in your example then

=SUMPRODUCT(--(YEAR(A1:A100)=2005))

or a specific month, e.g. December 2005

=SUMPRODUCT(--(TEXT(A1:A100,"mmm yy")="Dec 05"))

or for any date range where you have start date in H1 and end date in
H2

=SUMPRODUCT(--(A1:A100=H1),--(A1:A100<=H2))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=566840

Awesome it worked. Thank you so much, this will save me so much time....

"Ron Coderre" wrote:

Try something like this:

B1: 1/1/2005 (start date)
B2: 12/31/2005 (end date)
B3: =SUMPRODUCT((A1:A1000=B1)*(A1:A1000<=B2))

OR...to count all dates witin one year:
B3: =SUMPRODUCT(--(YEAR(A1:A1000)=2005))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"KC" wrote:

I need to calulate how many cells in one column have a date that are between
a range of dates for example 1/1/2005 through 12/31/2005.

Thanks.