Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells base on a predetermined range
Hello All,
Here's the dilemma. I have a set of dates in two columns. I can determine the data set from the first column but I can't figure out how to count the data in the second column based on the data set. Here's an example; date_rec date_paid 8/24/2005 9/14/2005 8/25/2005 10/5/2005 8/31/2005 9/14/2005 9/1/2005 10/26/2005 9/1/2005 10/19/2005 9/2/2005 9/14/2005 9/6/2005 10/26/2005 9/6/2005 9/21/2005 9/6/2005 10/5/2005 9/6/2005 10/5/2005 9/6/2005 10/5/2005 9/6/2005 9/14/2005 9/8/2005 10/5/2005 9/8/2005 9/21/2005 9/9/2005 9/21/2005 9/9/2005 9/28/2005 9/11/2005 10/26/2005 9/12/2005 9/28/2005 9/12/2005 9/12/2005 9/28/2005 9/12/2005 9/28/2005 9/13/2005 9/13/2005 9/28/2005 9/13/2005 9/28/2005 9/13/2005 9/21/2005 9/13/2005 10/5/2005 9/13/2005 9/13/2005 10/5/2005 9/13/2005 9/28/2005 9/13/2005 9/13/2005 9/21/2005 9/13/2005 9/21/2005 9/13/2005 9/21/2005 9/13/2005 9/21/2005 In an empty cell I have; =COUNTIF(date_rec,"<="&DATE(2005,9,13))-COUNTIF(date_rec,"<"&DATE(2005,9,7)) which gives me a 7 day date range. What I need to do from here is to count the corresponding populated cells in the date_paid range. Here is what I have tried but I just get a zero value; =COUNTIF(date_paid,(COUNTIF(date_rec,"<="&DATE(200 5,9,13))-COUNTIF(date_rec,"<"&DATE(2005,9,7)))) Any suggestions? Thanks, Bruno |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells base on a predetermined range
Hi!
Better to use cells to hold the date range: A1 = 9/7/2005 B1 = 9/13/2005 =SUMPRODUCT(--(date_rec=A1),--(date_rec<=B1),--(date_paid<"")) Biff wrote in message oups.com... Hello All, Here's the dilemma. I have a set of dates in two columns. I can determine the data set from the first column but I can't figure out how to count the data in the second column based on the data set. Here's an example; date_rec date_paid 8/24/2005 9/14/2005 8/25/2005 10/5/2005 8/31/2005 9/14/2005 9/1/2005 10/26/2005 9/1/2005 10/19/2005 9/2/2005 9/14/2005 9/6/2005 10/26/2005 9/6/2005 9/21/2005 9/6/2005 10/5/2005 9/6/2005 10/5/2005 9/6/2005 10/5/2005 9/6/2005 9/14/2005 9/8/2005 10/5/2005 9/8/2005 9/21/2005 9/9/2005 9/21/2005 9/9/2005 9/28/2005 9/11/2005 10/26/2005 9/12/2005 9/28/2005 9/12/2005 9/12/2005 9/28/2005 9/12/2005 9/28/2005 9/13/2005 9/13/2005 9/28/2005 9/13/2005 9/28/2005 9/13/2005 9/21/2005 9/13/2005 10/5/2005 9/13/2005 9/13/2005 10/5/2005 9/13/2005 9/28/2005 9/13/2005 9/13/2005 9/21/2005 9/13/2005 9/21/2005 9/13/2005 9/21/2005 9/13/2005 9/21/2005 In an empty cell I have; =COUNTIF(date_rec,"<="&DATE(2005,9,13))-COUNTIF(date_rec,"<"&DATE(2005,9,7)) which gives me a 7 day date range. What I need to do from here is to count the corresponding populated cells in the date_paid range. Here is what I have tried but I just get a zero value; =COUNTIF(date_paid,(COUNTIF(date_rec,"<="&DATE(200 5,9,13))-COUNTIF(date_rec,"<"&DATE(2005,9,7)))) Any suggestions? Thanks, Bruno |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated counting of cells (based on other cells contents) | Excel Worksheet Functions | |||
Select Range of Cells | Excel Discussion (Misc queries) | |||
counting a cell range within a range | Excel Worksheet Functions | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Counting empty cells within a range of cells | New Users to Excel |