Help with SUMIF using a range of dates as criteria
 

I'm using Excel 2000. Column A has four dates, Column B has the
person's name. I want to count how many dates in column A fall
between a date range. My data is below

A2 = 1-Apr-99
A3 = 1-Dec-99
A4 = 1-2Dec-99
A5 = 2-Jan-00

B2 = Mary
B3 = Peter
B4 = Paul
B5 = Susan

I want to know how many entries in column A fell between the dates of
April 1, 1999 and March 31, 2000, the count should be four. What
would be the appropriate COUNTIF formula? Pardon the pun but I've
lost count of how many variations I've tried to get my result. greatly
appreciated?

Any help would be Also, if some entries in column A were blank, would
that affect the formula?

Help with SUMIF using a range of dates as criteria
 

You can't use COUNTIF with multiple conditions. Assuming your column A data
is formatted as Dates (not text), this SUMPRODUCT formula should work:

=SUMPRODUCT(--(A2:A5=DATEVALUE("4/1/1999")),--(A2:A5<=DATEVALUE("3/31/2000")))

Hope this helps,

Hutch

" wrote:

I'm using Excel 2000. Column A has four dates, Column B has the
person's name. I want to count how many dates in column A fall
between a date range. My data is below

A2 = 1-Apr-99
A3 = 1-Dec-99
A4 = 1-2Dec-99
A5 = 2-Jan-00

B2 = Mary
B3 = Peter
B4 = Paul
B5 = Susan

I want to know how many entries in column A fell between the dates of
April 1, 1999 and March 31, 2000, the count should be four. What
would be the appropriate COUNTIF formula? Pardon the pun but I've
lost count of how many variations I've tried to get my result. greatly
appreciated?

Any help would be Also, if some entries in column A were blank, would
that affect the formula?

Help with SUMIF using a range of dates as criteria
 

=COUNTIF(A2:A5,"="&DATE(1999,4,1))-COUNTIF(A2:A5,""&DATE(2000,3,31))

or


=SUMPRODUCT(--(A2:A5=DATE(1999,4,1)),--(A2:A5<=DATE(2000,3,31)))

If you get a date using the former, just format the result as general

--
Regards,

Peo Sjoblom


wrote in message
ups.com...
I'm using Excel 2000. Column A has four dates, Column B has the
person's name. I want to count how many dates in column A fall
between a date range. My data is below

A2 = 1-Apr-99
A3 = 1-Dec-99
A4 = 1-2Dec-99
A5 = 2-Jan-00

B2 = Mary
B3 = Peter
B4 = Paul
B5 = Susan

I want to know how many entries in column A fell between the dates of
April 1, 1999 and March 31, 2000, the count should be four. What
would be the appropriate COUNTIF formula? Pardon the pun but I've
lost count of how many variations I've tried to get my result. greatly
appreciated?

Any help would be Also, if some entries in column A were blank, would
that affect the formula?

Help with SUMIF using a range of dates as criteria
 

Thanks for the suggestions. The first one worked and I'm going to try
the second suggested solution as well.