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.

 

[Jerry W. Lewis]

You cannot nest or specify multiple conditions with COUNTIF. The usual
workaround is to use SUMPRODUCT as in

=SUMPRODUCT(condition1*condition2*condition3...)

specifically

=SUMPRODUCT(($E$1:$E$1000<>"Closed")*($AA$1:$AA$1000="0 - 14 Days"))

would count the number of open orders <15 days.

Note that SUMPRODUCT cannot address an entire column, therefore you must
specify a row range (which may contain up to 65,535 rows).

Jerry