COUNTIF and Nested Functions?

[Larry Novida]

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.

 

[Biff]

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

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.

 

[Larry Novida]

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,

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

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.

 

[Biff]

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

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,

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

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.