Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Larry Novida
 
Posts: n/a
Default 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   Report Post  
Biff
 
Posts: n/a
Default

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   Report Post  
Larry Novida
 
Posts: n/a
Default

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   Report Post  
Biff
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Countif Function -Nested Angi Excel Discussion (Misc queries) 7 May 4th 05 07:04 PM
functions Dawn S Excel Worksheet Functions 12 January 13th 05 12:23 PM


All times are GMT +1. The time now is 11:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"