Counting number of days excluding certain values

  • Thread starter Thread starter Jarnell
  • Start date Start date
J

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.
 
Preliminary submittals have a "P" before the reference number.
I need help in filtering out the preliminary data which is not counted
Click to expand...

Add another test to the formulas:

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