Thanks for the tip! Your suggestion works like a charm! However, when I
add the following to the end of your formula:
-COUNTIF(E2:E65532,"< Closed") -COUNTIF(E2:E65532,"< *)
I get some wierd number. What I've done in the past is to first filter
column E to display rows < Closed. Then I've filtered column AA to display
rows =0 and <=14, etc... That's the intent of the formula I'm trying to
write.
Any other thoughts would be helpful.
Cheers,
"Biff" wrote in message
...
Hi!
The # of open days is already calculated and is stored in column $AA.
If the open days are already calculated you can just reference column AA
for
your calcs.
What you should do is create a small table somewhere with the ranges. I'll
use F1:G7 for that table.
F1..........G1
0............14
15..........30
31..........45
46..........60
61..........75
76..........90
91
In H1 enter this formula and copy down to H7:
=COUNTIF(AA:AA,"="&F1)-COUNTIF(AA:AA,""&G1)
Of course you'll need to adjust for the file name and sheet name.
Biff
"Larry Novida" wrote in message
...
I want to capture the number of orders closed, open, and how long (# of
days) the orders have been open. The # of open days is already
calculated
and is stored in column $AA.
The formulas are entered on a separate worksheet that also combines
information from other worksheets.
For the total # of orders, the following formula works just fine:
=COUNTIF('[DataSource.xls]Sheet1'!$A:$A,"*")-1
For the number of Closed orders, the following formula works just fine:
=COUNTIF('[DataSource.xls]Sheet1'!$E:$E,"Closed")-1
What I want to do now is get a count of the number of open orders based
on
the following ranges: 0 - 14 Days, 15 - 30 Days, 31 - 45 Days, 45 - 60
Days, 61 - 75 Days, 76 - 90 Days, and 90 Days. I started with:
=COUNTIF('[DataSource.xls]Sheet1'!$E:$E,"<Closed")-1
but I now need help with nesting the query so that I can get the count
of
orders for the different ranges.
I chose to use < Closed because there are also different order
statuses,
i.e. new, pending, in progress, cancelled, etc.
Any alternate/better ideas for doing this in Excel would be appreciated.
|