| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. . |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula to get number of days excluding Fridays in a given period | Excel Worksheet Functions | |||
| Counting # of days between 2 dates excluding Fri & Sat) | Excel Worksheet Functions | |||
| Subtracting number of days excluding the weekend | Excel Discussion (Misc queries) | |||
| How to sum number values, excluding data returned #N/A during Vloo | Excel Worksheet Functions | |||
| Counting days comparing 2 dates excluding empty cells | Excel Worksheet Functions | |||
Linear Mode
