Compare dates with lag

[adimar]

I need a formula to check for status=â€Closed†and delivery within 2 days of
requested date.

A B C
Status Requested Date Delivered Date
===== ============ ===========

Closed 10/16/07 13:31 12/5/07 15:17
Submitted 10/16/07 13:31 12/5/07 15:17
Submitted 10/16/07 13:31 12/5/07 15:17
Closed 10/16/07 16:11 12/18/07 16:29
Submitted 10/19/07 9:58 10/19/07 12:02
Submitted 10/19/07 9:58 10/19/07 12:08
Closed 10/19/07 9:58 10/19/07 12:08
Submitted 10/19/07 9:58 10/19/07 12:08
Closed 10/19/07 10:44 10/31/07 17:06
Submitted 10/19/07 10:44 10/31/07 17:06


Thank you.

 

[Bob Phillips]

=AND(A2="Closed",C2-B2<=2)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[adimar]

Can you please suggest a sumproduct format?

I need to count how many items in the array satisfy this condition.
When I attempt sumproduct I get a #VALUE error.
BTW, some dates may not be filled in, so I need to check valid date as well.

Thank you.

 

[Bob Phillips]

=SUMPRODUCT(--(A2:A20="Closed"),--(C2:C20-B2:B20<=2))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[adimar]

One more Question:

Since this one works fine:
RawData!$AF$1:$AF$10000>RawData!$AD$1:$AD$10000

Why does this return a #VALUE error?
RawData!$AF$1:$AF$10000-1>RawData!$AD$1:$AD$10000

Thank you.

 

[Bob Phillips]

(RawData!$AF$1:$AF$10-1)>RawData!$AD$1:$AD$10

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)