Sumproduct Formula

I don't really think so. It depends on how many rows and columns your
SUMPRODUCT's are looking at, but even with short row and column ranges, that
many SUMPRODUCT formulas are going to take forever to calculate. Any formula
based alternative, no matter how innovative would still be volatile and
therefore would still be very slow to complete calculation,
One possible way round it is to insert the formulas into the appropriate
places via a Macro and then in the same Macro copy and Paste Special Values
so that the file contains no formulas at all until the Macro(s) are run, and
indeed, no formulas after the Macro(s) are run.
You may perhaps be able to enter only the formulas that need to be
calculated in a certain circumstance rather than all of them at the same
time. This would speed up the calculation time significantly.
Obviously without being able to see your sheet its difficult to say if the
afore mentioned procedure would be feasible,
Regards,
Alan.

"Mike" <[email protected]> wrote in message
news:[email protected]...
>I have a workbook that uses over 3000 sumproduct formulas to extract
> data based on 7 different criteria. Works great except for calculation
> time.
>
> Is there an alternative to Sumproduct that would be faster and still
> allow for sumation based on multiple criteria?
>
> Is it possible to have Excel calculate certain cell ranges but not the
> entire workbook?
>
> Would additional RAM help? (I currently have 1 MB)
>
> Is there a way to identify things that make calc time longer?
>
> Thanks for any suggestions...
>

Have you tried using pivot tables? They are extremely quick and summarize
data similarly as SUMPRODUCT does.
--
Regards,

David Billigmeier



"Mike" wrote:

> I have a workbook that uses over 3000 sumproduct formulas to extract
> data based on 7 different criteria. Works great except for calculation
> time.
>
> Is there an alternative to Sumproduct that would be faster and still
> allow for sumation based on multiple criteria?
>
> Is it possible to have Excel calculate certain cell ranges but not the
> entire workbook?
>
> Would additional RAM help? (I currently have 1 MB)
>
> Is there a way to identify things that make calc time longer?
>
> Thanks for any suggestions...
>
>

One way is to reduce the number of ranges tested...

Example

Suppose we have

[A]

=SUMPRODUCT(($A$2:$A$1000=$K2)+0,($B$2:$B$1000=$L2)+0,$C$2:$C$1000)

and we want to reduce the number of ranges tested:

D2, copied down:

=A2&"#"&B2

The formula in [A] can be re-expressed as:

[B]

=SUMIF(($D$2:$D$1000,$K2&"#"&$L2,$C$2:$C$1000)

For more ideas, see:

http://tinyurl.com/d9eom

For computing on relevant subranges instead of whole range, the
following example might be helpful:

http://tinyurl.com/cqy47

And for more, see:

http://tinyurl.com/d9eom

Mike wrote:
> I have a workbook that uses over 3000 sumproduct formulas to extract
> data based on 7 different criteria. Works great except for calculation
> time.
>
> Is there an alternative to Sumproduct that would be faster and still
> allow for sumation based on multiple criteria?
>
> Is it possible to have Excel calculate certain cell ranges but not the
> entire workbook?
>
> Would additional RAM help? (I currently have 1 MB)
>
> Is there a way to identify things that make calc time longer?
>
> Thanks for any suggestions...
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

I don't really think so. It depends on how many rows and columns your
SUMPRODUCT's are looking at, but even with short row and column ranges, that
many SUMPRODUCT formulas are going to take forever to calculate. Any formula
based alternative, no matter how innovative would still be volatile and
therefore would still be very slow to complete calculation,
One possible way round it is to insert the formulas into the appropriate
places via a Macro and then in the same Macro copy and Paste Special Values
so that the file contains no formulas at all until the Macro(s) are run, and
indeed, no formulas after the Macro(s) are run.
You may perhaps be able to enter only the formulas that need to be
calculated in a certain circumstance rather than all of them at the same
time. This would speed up the calculation time significantly.
Obviously without being able to see your sheet its difficult to say if the
afore mentioned procedure would be feasible,
Regards,
Alan.

"Mike" <[email protected]> wrote in message
news:[email protected]...
>I have a workbook that uses over 3000 sumproduct formulas to extract
> data based on 7 different criteria. Works great except for calculation
> time.
>
> Is there an alternative to Sumproduct that would be faster and still
> allow for sumation based on multiple criteria?
>
> Is it possible to have Excel calculate certain cell ranges but not the
> entire workbook?
>
> Would additional RAM help? (I currently have 1 MB)
>
> Is there a way to identify things that make calc time longer?
>
> Thanks for any suggestions...
>

Have you tried using pivot tables? They are extremely quick and summarize
data similarly as SUMPRODUCT does.
--
Regards,

David Billigmeier



"Mike" wrote:

> I have a workbook that uses over 3000 sumproduct formulas to extract
> data based on 7 different criteria. Works great except for calculation
> time.
>
> Is there an alternative to Sumproduct that would be faster and still
> allow for sumation based on multiple criteria?
>
> Is it possible to have Excel calculate certain cell ranges but not the
> entire workbook?
>
> Would additional RAM help? (I currently have 1 MB)
>
> Is there a way to identify things that make calc time longer?
>
> Thanks for any suggestions...
>
>

One way is to reduce the number of ranges tested...

Example

Suppose we have

[A]

=SUMPRODUCT(($A$2:$A$1000=$K2)+0,($B$2:$B$1000=$L2)+0,$C$2:$C$1000)

and we want to reduce the number of ranges tested:

D2, copied down:

=A2&"#"&B2

The formula in [A] can be re-expressed as:

[B]

=SUMIF(($D$2:$D$1000,$K2&"#"&$L2,$C$2:$C$1000)

For more ideas, see:

http://tinyurl.com/d9eom

For computing on relevant subranges instead of whole range, the
following example might be helpful:

http://tinyurl.com/cqy47

And for more, see:

http://tinyurl.com/d9eom

Mike wrote:
> I have a workbook that uses over 3000 sumproduct formulas to extract
> data based on 7 different criteria. Works great except for calculation
> time.
>
> Is there an alternative to Sumproduct that would be faster and still
> allow for sumation based on multiple criteria?
>
> Is it possible to have Excel calculate certain cell ranges but not the
> entire workbook?
>
> Would additional RAM help? (I currently have 1 MB)
>
> Is there a way to identify things that make calc time longer?
>
> Thanks for any suggestions...
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

I have a workbook that uses over 3000 sumproduct formulas to extract
data based on 7 different criteria. Works great except for calculation
time.

Is there an alternative to Sumproduct that would be faster and still
allow for sumation based on multiple criteria?

Is it possible to have Excel calculate certain cell ranges but not the
entire workbook?

Would additional RAM help? (I currently have 1 MB)

Is there a way to identify things that make calc time longer?

Thanks for any suggestions...

I don't really think so. It depends on how many rows and columns your
SUMPRODUCT's are looking at, but even with short row and column ranges, that
many SUMPRODUCT formulas are going to take forever to calculate. Any formula
based alternative, no matter how innovative would still be volatile and
therefore would still be very slow to complete calculation,
One possible way round it is to insert the formulas into the appropriate
places via a Macro and then in the same Macro copy and Paste Special Values
so that the file contains no formulas at all until the Macro(s) are run, and
indeed, no formulas after the Macro(s) are run.
You may perhaps be able to enter only the formulas that need to be
calculated in a certain circumstance rather than all of them at the same
time. This would speed up the calculation time significantly.
Obviously without being able to see your sheet its difficult to say if the
afore mentioned procedure would be feasible,
Regards,
Alan.

"Mike" <[email protected]> wrote in message
news:[email protected]...
>I have a workbook that uses over 3000 sumproduct formulas to extract
> data based on 7 different criteria. Works great except for calculation
> time.
>
> Is there an alternative to Sumproduct that would be faster and still
> allow for sumation based on multiple criteria?
>
> Is it possible to have Excel calculate certain cell ranges but not the
> entire workbook?
>
> Would additional RAM help? (I currently have 1 MB)
>
> Is there a way to identify things that make calc time longer?
>
> Thanks for any suggestions...
>

Have you tried using pivot tables? They are extremely quick and summarize
data similarly as SUMPRODUCT does.
--
Regards,

David Billigmeier



"Mike" wrote:

> I have a workbook that uses over 3000 sumproduct formulas to extract
> data based on 7 different criteria. Works great except for calculation
> time.
>
> Is there an alternative to Sumproduct that would be faster and still
> allow for sumation based on multiple criteria?
>
> Is it possible to have Excel calculate certain cell ranges but not the
> entire workbook?
>
> Would additional RAM help? (I currently have 1 MB)
>
> Is there a way to identify things that make calc time longer?
>
> Thanks for any suggestions...
>
>

One way is to reduce the number of ranges tested...

Example

Suppose we have

[A]

=SUMPRODUCT(($A$2:$A$1000=$K2)+0,($B$2:$B$1000=$L2)+0,$C$2:$C$1000)

and we want to reduce the number of ranges tested:

D2, copied down:

=A2&"#"&B2

The formula in [A] can be re-expressed as:

[B]

=SUMIF(($D$2:$D$1000,$K2&"#"&$L2,$C$2:$C$1000)

For more ideas, see:

http://tinyurl.com/d9eom

For computing on relevant subranges instead of whole range, the
following example might be helpful:

http://tinyurl.com/cqy47

And for more, see:

http://tinyurl.com/d9eom

Mike wrote:
> I have a workbook that uses over 3000 sumproduct formulas to extract
> data based on 7 different criteria. Works great except for calculation
> time.
>
> Is there an alternative to Sumproduct that would be faster and still
> allow for sumation based on multiple criteria?
>
> Is it possible to have Excel calculate certain cell ranges but not the
> entire workbook?
>
> Would additional RAM help? (I currently have 1 MB)
>
> Is there a way to identify things that make calc time longer?
>
> Thanks for any suggestions...
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.