Counting with Date Range as Criteria
 

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.

Counting with Date Range as Criteria
 

See my ans to your PREVIOUS post
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Julie" wrote in message
...
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.

Counting with Date Range as Criteria
 

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
---
"Julie" wrote:
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.

Counting with Date Range as Criteria
 

On Fri, 16 Oct 2009 14:04:01 -0700, Julie
wrote:

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,Succes sful,"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

Counting with Date Range as Criteria
 

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.

Counting with Date Range as Criteria
 

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
---
"Julie" wrote:
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.