Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Complicated counting of cells (based on other cells contents) George Excel Worksheet Functions 3 November 7th 05 07:39 PM
Select Range of Cells programmingrookie Excel Discussion (Misc queries) 2 August 11th 05 07:13 PM
counting a cell range within a range mmay321 Excel Worksheet Functions 2 August 10th 05 03:56 PM
Counting empty cells within a range of cells Rosehill - ExcelForums.com New Users to Excel 2 May 2nd 05 08:53 AM
Counting empty cells within a range of cells Rosehill - ExcelForums.com New Users to Excel 0 April 7th 05 12:47 AM


All times are GMT +1. The time now is 10:26 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"