count between two dates

[capt]

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?

 

[Max]

Try something like this:
=IF(COUNT(P3,R3)<2,"",SUMPRODUCT((MAIN!$B$10:$B$4999>=P3)*(MAIN!$B$10:$B$4999<=R3)))
where P3, R3 houses the start and end dates

 

[Tim879]

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)

 

[Bernard Liengme]

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

 

[capt]

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

 

[Bernard Liengme]

And where are the dates relative to the B range? We need to know what you
data looks like.

 

[capt]

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.....

 

[Max]

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

 

[capt]

Brilliant Max it works fine.
Thank you
--
capt

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]

welcome, glad it helped.