count between two dates
 

I have the following formula.

=IF($A6="","",COUNTIF(MAIN!$B$10:$B$4999,"" &$A6 & ""))

How do I count between two dates in cells: P3 and R3?
--
capt

count between two dates
 

Try something like this:
=IF(COUNT(P3,R3)<2,"",SUMPRODUCT((MAIN!$B$10:$B$49 99=P3)*(MAIN!$B$10:$B$4999<=R3)))
where P3, R3 houses the start and end dates
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"capt" wrote:
I have the following formula.

=IF($A6="","",COUNTIF(MAIN!$B$10:$B$4999,"" &$A6 & ""))

How do I count between two dates in cells: P3 and R3?
--
capt

count between two dates
 

From your post below - I wasn't really sure what you were looking
for....

If you're looking for the # of days between 2 dates, you can just
subtract Date 1 from Date 2. If you're looking for the number of
working days, enable the Analysis Toolpack Addin (tools - Addins -
Anaysis Toolpack) and then use the networkdays() function to calculate
the number of days, excluding weekends, between the 2 dates.

If you are looking for the number of rows of data between 2 dates on
your spreadsheet, the following will work:

Assume you have dates in Cells A1: A6 as follows:
1/1/2007
2/1/2007
2/15/2007
3/1/2007
4/9/2007
12/31/2007



In cells B1 and B2 I put the 2 dates you want to search between
2/1/2007
3/1/2007



This formula counts the # rows between the 2 dates entered:
=COUNTIF($A$1:$A$6,"="&B1)-COUNTIF($A$1:$A$6,""&B2)





On Dec 10, 9:55 am, capt wrote:
I have the following formula.

=IF($A6="","",COUNTIF(MAIN!$B$10:$B$4999,"" &$A6 & ""))

How do I count between two dates in cells: P3 and R3?
--
capt

count between two dates
 

The formula you show counts how many times the B range in the Main worksheet
match the value in A6 of the sheet where the formula resides. If A6 is empty
you see a blank cell.

Now tell us what you mean by: How do I count between two dates in cells: P3
and R3?

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"capt" wrote in message
...
I have the following formula.

=IF($A6="","",COUNTIF(MAIN!$B$10:$B$4999,"" &$A6 & ""))

How do I count between two dates in cells: P3 and R3?
--
capt

count between two dates
 

In cells P3 and R3 I insert a date From and To. The B range is where I find
the Names of clients. cell A6 will carry one of the names of the client.
I after to count how many entries there are of a client, in col B, between
two dates.

Thank you
--
capt


"Bernard Liengme" wrote:

The formula you show counts how many times the B range in the Main worksheet
match the value in A6 of the sheet where the formula resides. If A6 is empty
you see a blank cell.

Now tell us what you mean by: How do I count between two dates in cells: P3
and R3?

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"capt" wrote in message
...
I have the following formula.

=IF($A6="","",COUNTIF(MAIN!$B$10:$B$4999,"" &$A6 & ""))

How do I count between two dates in cells: P3 and R3?
--
capt

count between two dates
 

And where are the dates relative to the B range? We need to know what you
data looks like.
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"capt" wrote in message
...
In cells P3 and R3 I insert a date From and To. The B range is where I
find
the Names of clients. cell A6 will carry one of the names of the client.
I after to count how many entries there are of a client, in col B, between
two dates.

Thank you
--
capt


"Bernard Liengme" wrote:

The formula you show counts how many times the B range in the Main
worksheet
match the value in A6 of the sheet where the formula resides. If A6 is
empty
you see a blank cell.

Now tell us what you mean by: How do I count between two dates in cells:
P3
and R3?

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"capt" wrote in message
...
I have the following formula.

=IF($A6="","",COUNTIF(MAIN!$B$10:$B$4999,"" &$A6 & ""))

How do I count between two dates in cells: P3 and R3?
--
capt

count between two dates
 

Below is a small part of the table. I need to count each client (col B)
relative to the dates.
ie between 5-sep-07 and 10-sep-07
client A = 4
client B = 3

A B C D
Date Client Hours Total
5-Sep-07 A 5:20 5:20
7-Sep-07 B 5:15 10:35
8-Sep-07 A 10:05 20:40
9-Sep-07 B 0:35 21:15
9-Sep-07 B 7:40 28:55
9-Sep-07 A 0:15 29:10
10-Sep-07 A 0:15 29:25
and so on.....

--
capt


"Bernard Liengme" wrote:

And where are the dates relative to the B range? We need to know what you
data looks like.
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"capt" wrote in message
...
In cells P3 and R3 I insert a date From and To. The B range is where I
find
the Names of clients. cell A6 will carry one of the names of the client.
I after to count how many entries there are of a client, in col B, between
two dates.

Thank you
--
capt


"Bernard Liengme" wrote:

The formula you show counts how many times the B range in the Main
worksheet
match the value in A6 of the sheet where the formula resides. If A6 is
empty
you see a blank cell.

Now tell us what you mean by: How do I count between two dates in cells:
P3
and R3?

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"capt" wrote in message
...
I have the following formula.

=IF($A6="","",COUNTIF(MAIN!$B$10:$B$4999,"" &$A6 & ""))

How do I count between two dates in cells: P3 and R3?
--
capt

count between two dates
 

Something like this should work for you:

In say, T3:
=IF(COUNTA(P3,R3,S3)<3,"",SUMPRODUCT((MAIN!$A$10:$ A$4999=P3)*(MAIN!$A$10:$A$4999<=R3)*(MAIN!$B$10:$ B$4999=S3))

where P3, R3 houses the start and end dates,
S3 contains the client, eg: A

Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"capt" wrote:
Below is a small part of the table. I need to count each client (col B)
relative to the dates.
ie between 5-sep-07 and 10-sep-07
client A = 4
client B = 3

A B C D
Date Client Hours Total
5-Sep-07 A 5:20 5:20
7-Sep-07 B 5:15 10:35
8-Sep-07 A 10:05 20:40
9-Sep-07 B 0:35 21:15
9-Sep-07 B 7:40 28:55
9-Sep-07 A 0:15 29:10
10-Sep-07 A 0:15 29:25
and so on.....

count between two dates
 

Brilliant Max it works fine.
Thank you
--
capt


"Max" wrote:

Something like this should work for you:

In say, T3:
=IF(COUNTA(P3,R3,S3)<3,"",SUMPRODUCT((MAIN!$A$10:$ A$4999=P3)*(MAIN!$A$10:$A$4999<=R3)*(MAIN!$B$10:$ B$4999=S3))

where P3, R3 houses the start and end dates,
S3 contains the client, eg: A

Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"capt" wrote:
Below is a small part of the table. I need to count each client (col B)
relative to the dates.
ie between 5-sep-07 and 10-sep-07
client A = 4
client B = 3

A B C D
Date Client Hours Total
5-Sep-07 A 5:20 5:20
7-Sep-07 B 5:15 10:35
8-Sep-07 A 10:05 20:40
9-Sep-07 B 0:35 21:15
9-Sep-07 B 7:40 28:55
9-Sep-07 A 0:15 29:10
10-Sep-07 A 0:15 29:25
and so on.....

count between two dates
 

welcome, glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"capt" wrote in message
...
Brilliant Max it works fine.
Thank you