Home |
Search |
Today's Posts |
#1
|
|||
|
|||
COUNTIF and Nested Functions?
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
Hi!
Try this instead: =SUMPRODUCT(--(E2:E65532<"Closed"),--(AA2:AA65532=F1),--(AA2:AA65532<=G1)) Note that Sumproduct will not accept whole columns as arguments. eg: E:E, AA:AA Biff "Larry Novida" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif Function -Nested | Excel Discussion (Misc queries) | |||
functions | Excel Worksheet Functions |