Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting number of rows between date range and meeting 2 string criteria | Excel Worksheet Functions | |||
counting a number of cells with 2 range criteria | Excel Discussion (Misc queries) | |||
Conditional Formula, counting one criteria in a range based on ano | Excel Worksheet Functions | |||
Counting cells in a range per multiple criteria . . . | Excel Worksheet Functions | |||
Counting from one range to another range, multiple criteria | Excel Discussion (Misc queries) |