Counting number of days excluding certain values

[Jarnell]

I have a spreadsheet with a number of columns.

Col A Col D Col AB Col AD
Ref # Date Rec Days Open Date Closed
2281 4/30/10 11
2250 4/27/10 7 5/6/10
P2239 4/28/10 8 5/10/10
P2247 1/8/10 2 1/12/10
2258 4/23/10 16

I have a section for review time with two cells, one for 10 days or less and
one for more than 10 days. Right now it does not distinguish between the
"Offical" and "Preliminary" submittals. Preliminary submittals have a "P"
before the reference number. These submittals do not have a deadline
timeframe.

The current equation for the 10 days or less is:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(AD6,ROW(AD6:AD988)-ROW(AD6),,1)),--(AB6:AB988<=10))

and the one for 10 days or more is:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(AD6,ROW(AD6:AD988)-ROW(AD6),,1)),--(AB6:AB988>10))

I need help in filtering out the preliminary data which is not counted in
our number of days open. Thanks.

 

[T. Valko]

Preliminary submittals have a "P" before the reference number.

I need help in filtering out the preliminary data which is not counted

Add another test to the formulas:

--(LEFT(A6:A988)<>"P")

 

[Jarnell]

Thank you so much. This worked!!!!

Add another test to the formulas:

--(LEFT(A6:A988)<>"P")

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

You're welcome. Thanks for the feedback!