Okay. I have never had a class in excel or read a book and have had to figure
out ways of doing things on my own. This project is INSANE. I have a very
complicated spreadsheet that I was assigned. I will try my best to explain
this:

There are several different offices which all have different number series
ranges for their files. For instance, Imperial "C" has 2010000 - 2059999, but
Inland Empire "C" has 2800000 - 2899999 and 2600000 - 2699999, as well as 7
more different, gapped number series ranges for just that office. There are 8
different offices for "C". The "C" is a company code. There is also a company
code "L" and there are also 8 different offices for that company. Company
code "C" will only have one office assigned to a number series, but company
code "L" may have a number series assigned to it that overlaps a number
series for company code "C". "L" will not overlap "L" and "C" will not
overlap "C", but "L" may overlap "C" and vice versa. Files are recorded on
one day, then recieved by us, then processed and sent back to the same office
that sent them to us. The dates, company codes and order numbers are in a
sheet that is defined by a database query to an Access Database that users
enter the information into. My mission is to determine how long offices are
taking to send us the packets (Recorded Date to Recieved Date) broken down
like: Less Then 5 Days, 6 - 10 days, 11 - 15 days and so on all the way up to
31+ days. Also how long it takes us to send the packets back to the office
after we recieve them (Received Date to Sent Back Date) broken down by Less
Than 30 Days and then then by weeks (I used days in my formulas to make it
easier) all the way up to 10 Weeks +.

I set up one sheet for the user to select from a combo box the office of the
information they want to see and all the information is displayed below. The
formulas below are all just sums of the formulas in the "FS" sheet I talk
about later.

I set up another sheet (Ranges) to only contain a definition of what number
series ranges apply to which offices. The first column of this sheet contains
the L or C and the second column contains the name of the office. Columns C
through T contain number series range beginning and endings for each office.
Column C is a beginning number, D is an ending number, E is a beginning
number, F is an ending number and so on.

Another sheet (FS) is where all my array formulas are. The first row is
dedicated to lookups. Using the combo box on the first sheet, the user
selects the office they want to see and the the lookup formulas look at the
Ranges sheet to determine what Ranges and company code applies to that
office. These formulas go all the way over to U1 and if an office, such as
Imperial has only one number range series, the remaining cells are filled
with 0's. A few rows beneath this are my array formulas. Below is an example
of one of the array formulas. This one is used to determine how many files
were sent to us within 6 to 10 days of its recording date using the first
number series range. I then copied and pasted the formula two columns to the
right to get the count for the next number series range and so on until I had
the count for every number series range. The totals of these are displayed on
the first page. RPL is the name of the sheet containing (in this order)
A=Received Date, B=Recording Date, C=File Number, D=Company Code, E=Box#
(irrelevant), F=To_IC (irrelevant), G=From_IC (irrelevant), H=Back_to_Site.
FS!$A$1 is "C" or "L".

{=COUNT(IF(RPL!$C$2:$C$15160=FS!B1,IF(RPL!$C$2:$C $15160<=FS!C1,IF(RPL!$D$2:$D$15160=FS!$A$1,IF(RPL! $A$2:$A
$15160-RPL!$B$2:$B$15160=6,IF(RPL!$A$2:$A$15160-RPL!$B$2:$B$15160<=10,IF(RPL!$C$2:$C$15160<0,RPL! $C$2:$C$15160)))))))}

This is my array formula for determining a count our turnaround time for
sending packets Back_to_Site after they have been received that was from 31
to 35 days. It is then continued across to U just like the other array
formula to calculate for all number range series possibilities and just like
the other array formulas is then adjusted in the rows below for 36 to 40, 41
to 50 and so on:

{=COUNT(IF(RPL!$C$2:$C$15160=FS!B1,IF(RPL!$C$2:$C $15160<=FS!C1,IF(RPL!$D$2:$D$15160=FS!$A$1,IF(RPL! $H$2:$H
$15160-RPL!$A$2:$A$15160=31,IF(RPL!$H$2:$H$15160-RPL!$A$2:$A$15160<=35,IF(RPL!$C$2:$C$15160<0,RPL! $C$2:$C$15160)))))))}

Still alive? My formulas work fine and my counts come back accurately, but
the array formulas take so long to calculate that I need to know if there is
a better way of going about this that takes less time to calculate. PLEASE
SOMEONE ANSWER ME! Thank you for reading this.
--
We are the music makers... and we are the dreamers of dreams.

Maybe you could replace the arrays with a formula for each criteria,

=AND(C2=B1,C2<=C1,D2=A1,and so on) for each line

then to count just use

=COUNTIF(formula_range,TRUE)


note that I didn't use any sheet names but in general it is faster to test
each row/column and the count the result of those formulas

here's a link

http://www.decisionmodels.com/optspeedb.htm



--
Regards,

Peo Sjoblom

(No private emails please)


"belly0fdesire" wrote in message
...
Okay. I have never had a class in excel or read a book and have had to
figure
out ways of doing things on my own. This project is INSANE. I have a very
complicated spreadsheet that I was assigned. I will try my best to explain
this:

There are several different offices which all have different number series
ranges for their files. For instance, Imperial "C" has 2010000 - 2059999,
but
Inland Empire "C" has 2800000 - 2899999 and 2600000 - 2699999, as well as
7
more different, gapped number series ranges for just that office. There
are 8
different offices for "C". The "C" is a company code. There is also a
company
code "L" and there are also 8 different offices for that company. Company
code "C" will only have one office assigned to a number series, but
company
code "L" may have a number series assigned to it that overlaps a number
series for company code "C". "L" will not overlap "L" and "C" will not
overlap "C", but "L" may overlap "C" and vice versa. Files are recorded on
one day, then recieved by us, then processed and sent back to the same
office
that sent them to us. The dates, company codes and order numbers are in a
sheet that is defined by a database query to an Access Database that users
enter the information into. My mission is to determine how long offices
are
taking to send us the packets (Recorded Date to Recieved Date) broken down
like: Less Then 5 Days, 6 - 10 days, 11 - 15 days and so on all the way up
to
31+ days. Also how long it takes us to send the packets back to the office
after we recieve them (Received Date to Sent Back Date) broken down by
Less
Than 30 Days and then then by weeks (I used days in my formulas to make it
easier) all the way up to 10 Weeks +.

I set up one sheet for the user to select from a combo box the office of
the
information they want to see and all the information is displayed below.
The
formulas below are all just sums of the formulas in the "FS" sheet I talk
about later.

I set up another sheet (Ranges) to only contain a definition of what
number
series ranges apply to which offices. The first column of this sheet
contains
the L or C and the second column contains the name of the office. Columns
C
through T contain number series range beginning and endings for each
office.
Column C is a beginning number, D is an ending number, E is a beginning
number, F is an ending number and so on.

Another sheet (FS) is where all my array formulas are. The first row is
dedicated to lookups. Using the combo box on the first sheet, the user
selects the office they want to see and the the lookup formulas look at
the
Ranges sheet to determine what Ranges and company code applies to that
office. These formulas go all the way over to U1 and if an office, such as
Imperial has only one number range series, the remaining cells are filled
with 0's. A few rows beneath this are my array formulas. Below is an
example
of one of the array formulas. This one is used to determine how many files
were sent to us within 6 to 10 days of its recording date using the first
number series range. I then copied and pasted the formula two columns to
the
right to get the count for the next number series range and so on until I
had
the count for every number series range. The totals of these are displayed
on
the first page. RPL is the name of the sheet containing (in this order)
A=Received Date, B=Recording Date, C=File Number, D=Company Code, E=Box#
(irrelevant), F=To_IC (irrelevant), G=From_IC (irrelevant),
H=Back_to_Site.
FS!$A$1 is "C" or "L".

{=COUNT(IF(RPL!$C$2:$C$15160=FS!B1,IF(RPL!$C$2:$C $15160<=FS!C1,IF(RPL!$D$2:$D$15160=FS!$A$1,IF(RPL! $A$2:$A
$15160-RPL!$B$2:$B$15160=6,IF(RPL!$A$2:$A$15160-RPL!$B$2:$B$15160<=10,IF(RPL!$C$2:$C$15160<0,RPL! $C$2:$C$15160)))))))}

This is my array formula for determining a count our turnaround time for
sending packets Back_to_Site after they have been received that was from
31
to 35 days. It is then continued across to U just like the other array
formula to calculate for all number range series possibilities and just
like
the other array formulas is then adjusted in the rows below for 36 to 40,
41
to 50 and so on:

{=COUNT(IF(RPL!$C$2:$C$15160=FS!B1,IF(RPL!$C$2:$C $15160<=FS!C1,IF(RPL!$D$2:$D$15160=FS!$A$1,IF(RPL! $H$2:$H
$15160-RPL!$A$2:$A$15160=31,IF(RPL!$H$2:$H$15160-RPL!$A$2:$A$15160<=35,IF(RPL!$C$2:$C$15160<0,RPL! $C$2:$C$15160)))))))}

Still alive? My formulas work fine and my counts come back accurately, but
the array formulas take so long to calculate that I need to know if there
is
a better way of going about this that takes less time to calculate. PLEASE
SOMEONE ANSWER ME! Thank you for reading this.
--
We are the music makers... and we are the dreamers of dreams.