I need to find quantity of Letters between some period of time (one week)
from 21-09-09 until 27-09-09, which are not replied (Cells with Reply date
are blank)

Letter No Received Date Reply Date
L-255 21-09-2009
L-256 23-09-2009 25-09-2009
L-257 25-09-2009 27-09-2009
L-258 21-09-2009
L-259 22-09-2009 25-09-2009
L-260 26-09-2009

Jul wrote:
I need to find quantity of Letters between some period of time (one week)
from 21-09-09 until 27-09-09, which are not replied (Cells with Reply date
are blank)

Letter No Received Date Reply Date
L-255 21-09-2009
L-256 23-09-2009 25-09-2009
L-257 25-09-2009 27-09-2009
L-258 21-09-2009
L-259 22-09-2009 25-09-2009
L-260 26-09-2009

Try using a Filter and Filter to show all Blanks.That's the simplest way I
can think of it.
DataFilterAutoFilter

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200910/1

Hi,

With your start date in E1 and end date in f1, try this

=SUMPRODUCT((B2:B7=D1)*(B2:B7<=E1)*(C2:C7<""))

or if your looking for cells in column c that have a date try this

=SUMPRODUCT((B2:B7=D1)*(B2:B7<=E1)*(ISNUMBER(C2:C 7)))

Mike

"Jul" wrote:

I need to find quantity of Letters between some period of time (one week)
from 21-09-09 until 27-09-09, which are not replied (Cells with Reply date
are blank)

Letter No Received Date Reply Date
L-255 21-09-2009
L-256 23-09-2009 25-09-2009
L-257 25-09-2009 27-09-2009
L-258 21-09-2009
L-259 22-09-2009 25-09-2009
L-260 26-09-2009

Or maybe this

=SUMPRODUCT((B2:B7=D1)*(B2:B7<=E1)*(C2:C7=""))

Mike

"Jul" wrote:

I need to find quantity of Letters between some period of time (one week)
from 21-09-09 until 27-09-09, which are not replied (Cells with Reply date
are blank)

Letter No Received Date Reply Date
L-255 21-09-2009
L-256 23-09-2009 25-09-2009
L-257 25-09-2009 27-09-2009
L-258 21-09-2009
L-259 22-09-2009 25-09-2009
L-260 26-09-2009

Hi

With date in A1:C7, this formula should do it:

=SUMPRODUCT(--(B2:B7=DATEVALUE("21-09-2009")),--(B2:B7<=DATEVALUE("27-09-2009")),--(C2:C7<""))

Regards,
Per

"Jul" skrev i meddelelsen
...
I need to find quantity of Letters between some period of time (one week)
from 21-09-09 until 27-09-09, which are not replied (Cells with Reply date
are blank)

Letter No Received Date Reply Date
L-255 21-09-2009
L-256 23-09-2009 25-09-2009
L-257 25-09-2009 27-09-2009
L-258 21-09-2009
L-259 22-09-2009 25-09-2009
L-260 26-09-2009

This way stops the need to enter the date in the formula

If E1:f4 contains

Start_Date N Days
21/09/2009 6
Replies Left
4

The formula in E1 refers to the date in E2 and the number f days to inspect
in F2.

=(SUMPRODUCT(--(Received_Dates=E2))-SUMPRODUCT(--(Received_Dates=E2+F2)))-(SUMPRODUCT(--(Reply_Dates=E2))-SUMPRODUCT(--(Reply_Dates=E2+F2)))

With dynamic range names for received and reply dates.

Received dates =OFFSET(Sheet6!$B$2,0,0,COUNTA(Sheet6!$B:$B)-1)
Reply Dates =OFFSET(Received_Dates,0,1)

You will need to change the Sheet name on with the ranges occur for the
first name.
To insert a dynamic range name:
in xl2003

Choose Insert, Name, Define. In the Names in Workbook text box type your name.
In the Refers to Paste the formula
Click Add
Type the Second range name in the 'Names in Workbook' text box
Type or paste the 2nd Formula
Click OK

In xl2007
Select the Formulas Tab
choose Name Manager
Click New

and follow the steps above.

HTH
Peter Atherton


"Jul" wrote:

I need to find quantity of Letters between some period of time (one week)
from 21-09-09 until 27-09-09, which are not replied (Cells with Reply date
are blank)

Letter No Received Date Reply Date
L-255 21-09-2009
L-256 23-09-2009 25-09-2009
L-257 25-09-2009 27-09-2009
L-258 21-09-2009
L-259 22-09-2009 25-09-2009
L-260 26-09-2009