Counting with Date Range as Criteria

[Julie]

A B
1 Date Successful
2 10/1/09 Y
3 10/1/09 N
4 10/5/09 Y
5 11/3/09 Y

I want to put a formula into a worksheet called "Analysis" to count the
number of Y (successful) records for a given date range in a worksheet called
"Master".

So, I want the Anaylsis worksheet to show me "2" when I ask for the number
of Y's in column B for the date range 10/1/09 to 10/30/09 in column A.

 

[Don Guillett]

See my ans to your PREVIOUS post

 

[Max]

If its to calc/check for a particular month-year, eg within Oct 09, you could
use something like this in Analysis:
=SUMPRODUCT((TEXT(Master!A2:A10,"mmmyy")="Oct09")*(Master!B2:B10="Y"))
Adapt the ranges to suit your actual data extents in Master
Voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Ron Rosenfeld]

A B
1 Date Successful
2 10/1/09 Y
3 10/1/09 N
4 10/5/09 Y
5 11/3/09 Y

I want to put a formula into a worksheet called "Analysis" to count the
number of Y (successful) records for a given date range in a worksheet called
"Master".

So, I want the Anaylsis worksheet to show me "2" when I ask for the number
of Y's in column B for the date range 10/1/09 to 10/30/09 in column A.

Where Date and Successful are the names of your two ranges on Master, and
StartDt and EndDt define the range of dates to check:

=SUMPRODUCT(--(Date>=StartDt),--(Date<=EndDt),--(Successful="Y"))

Or, if you have Excel 2007 or higher:

=COUNTIFS(Date,">="&StartDt,Date,"<="&EndDt,Successful,"Y")

If you are always going to be looking at whole months, you could consider using
a Pivot Table also. But the result will not be dynamic unless you had some
auto-update VBA macros. You can make it look pretty, though.
--ron

 

[Julie]

Thanks for the help everyone. Can we take it one step further? What if I
want to count any record that has something entered into column B, no matter
what it is, for the October date range? In other words, all the NonBlanks in
column B for a given date range?

I very much appreciate it.

 

[Max]

You could use this:
=SUMPRODUCT((TEXT(Master!A2:A10,"mmmyy")="Oct09")*(TRIM(Master!B2:B10)<>""))

The TRIM is an added precaution to avert spurious results due to presence of
any pure white spaces within col B
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik