Counting number of days excluding certain values
 

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:AB98810))

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

Counting number of days excluding certain values
 

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")

--
Biff
Microsoft Excel MVP


"Jarnell" wrote in message
...
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:AB98810))

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

Counting number of days excluding certain values
 

Thank you so much. This worked!!!!

"T. Valko" wrote:

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")

--
Biff
Microsoft Excel MVP


"Jarnell" wrote in message
...
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:AB98810))

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



.

Counting number of days excluding certain values
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Jarnell" wrote in message
...
Thank you so much. This worked!!!!

"T. Valko" wrote:

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")

--
Biff
Microsoft Excel MVP


"Jarnell" wrote in message
...
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:AB98810))

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



.