SumProduct making things work slow
 

I was using DSUM to sum financial transactions based on a variety of
criteria, many of which involved dates. The problem for this is that I would
have to make a variety of tables of DSUM criteria (I apologize if I'm not
using the right terminology here) for each week, so to set the worksheet up
for an entire year would be tedious and time consuming, not to mention have a
plethera of potential for careless errors.

And so, I created a new worksheet to do the same thing using SUMPRODUCT, and
while this worksheet has the advantage of being updatable for a new week
simply by dragging an existing cell into the one below it, after entering a
new transaction, the cursor freezes for several seconds and at the bottom of
the screen it says 'calculating cells xx%' which takes time to raise to 100%.


So, in short, I have two solutions to the problem: Using DSUM makes the
worksheet hard to update, and using SUMPRODUCT makes the worksheet hard to
use. Is it simply a matter of changing a feature to allow new input while
cells are being calculated? Why is SUMPRODUCT so inefficient? Is there
another, equally mobile solution that is more efficient?

Any suggestions/similar experiences would be appreciated. Thank You.

The solution to every problem with excel
 

Is to post your problem on here and continue searching for the answer on your
own. Once you post your question and help is on its way, you will inevitably
figure it out on your own within a matter of minutes.

I can just turn off automatic cell calculation under 'options' and hit F9 to
calculate my totals, which is what I'll probably do.

Nonetheless, a more efficient formula would improve my worksheet and
suggestions are still very much appreciated.


"Adam Graham" wrote:

I was using DSUM to sum financial transactions based on a variety of
criteria, many of which involved dates. The problem for this is that I would
have to make a variety of tables of DSUM criteria (I apologize if I'm not
using the right terminology here) for each week, so to set the worksheet up
for an entire year would be tedious and time consuming, not to mention have a
plethera of potential for careless errors.

And so, I created a new worksheet to do the same thing using SUMPRODUCT, and
while this worksheet has the advantage of being updatable for a new week
simply by dragging an existing cell into the one below it, after entering a
new transaction, the cursor freezes for several seconds and at the bottom of
the screen it says 'calculating cells xx%' which takes time to raise to 100%.


So, in short, I have two solutions to the problem: Using DSUM makes the
worksheet hard to update, and using SUMPRODUCT makes the worksheet hard to
use. Is it simply a matter of changing a feature to allow new input while
cells are being calculated? Why is SUMPRODUCT so inefficient? Is there
another, equally mobile solution that is more efficient?

Any suggestions/similar experiences would be appreciated. Thank You.

The solution to every problem with excel
 

A few comments:
It would be helpful to know how many criteria fields you have. A better
choice may be =sumif or a pivot table. Sumproduct is not really a query tool
-- you can use it that way as an array function, but that's not really what
it's for. Also, you could use =sum as an array as well, but that get's tricky
too. The DSUM implementation in excel was poorly thought thru way back in
the early 90s, and so you're right that you wind up with a bunch of
repetitive criteria all over the place.
Jason



"Adam Graham" wrote:

Is to post your problem on here and continue searching for the answer on your
own. Once you post your question and help is on its way, you will inevitably
figure it out on your own within a matter of minutes.

I can just turn off automatic cell calculation under 'options' and hit F9 to
calculate my totals, which is what I'll probably do.

Nonetheless, a more efficient formula would improve my worksheet and
suggestions are still very much appreciated.


"Adam Graham" wrote:

I was using DSUM to sum financial transactions based on a variety of
criteria, many of which involved dates. The problem for this is that I would
have to make a variety of tables of DSUM criteria (I apologize if I'm not
using the right terminology here) for each week, so to set the worksheet up
for an entire year would be tedious and time consuming, not to mention have a
plethera of potential for careless errors.

And so, I created a new worksheet to do the same thing using SUMPRODUCT, and
while this worksheet has the advantage of being updatable for a new week
simply by dragging an existing cell into the one below it, after entering a
new transaction, the cursor freezes for several seconds and at the bottom of
the screen it says 'calculating cells xx%' which takes time to raise to 100%.


So, in short, I have two solutions to the problem: Using DSUM makes the
worksheet hard to update, and using SUMPRODUCT makes the worksheet hard to
use. Is it simply a matter of changing a feature to allow new input while
cells are being calculated? Why is SUMPRODUCT so inefficient? Is there
another, equally mobile solution that is more efficient?

Any suggestions/similar experiences would be appreciated. Thank You.

SumProduct making things work slow
 

Some links on optimization:-

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



http://www.ozgrid.com/News/GoodVsBad...edUpEvents.htm



An excerpt:



"Below is a list of what I believe to be the worst offenders for slowing down recalculations of spreadsheets.

1.. Array formulas

2.. UDFs (User-defined functions)

3.. Volatile functions

4.. Using range references in formulas that are beyond the required data scope."

Jason mentioned Pivot Table and I found the following excerpt.

"...... In particular, the use of Excel's pivot tables and sub-totals can often be used in place of potentially hundreds of formulas."

Epinn

"Adam Graham" wrote in message ...
I was using DSUM to sum financial transactions based on a variety of
criteria, many of which involved dates. The problem for this is that I would
have to make a variety of tables of DSUM criteria (I apologize if I'm not
using the right terminology here) for each week, so to set the worksheet up
for an entire year would be tedious and time consuming, not to mention have a
plethera of potential for careless errors.

And so, I created a new worksheet to do the same thing using SUMPRODUCT, and
while this worksheet has the advantage of being updatable for a new week
simply by dragging an existing cell into the one below it, after entering a
new transaction, the cursor freezes for several seconds and at the bottom of
the screen it says 'calculating cells xx%' which takes time to raise to 100%.


So, in short, I have two solutions to the problem: Using DSUM makes the
worksheet hard to update, and using SUMPRODUCT makes the worksheet hard to
use. Is it simply a matter of changing a feature to allow new input while
cells are being calculated? Why is SUMPRODUCT so inefficient? Is there
another, equally mobile solution that is more efficient?

Any suggestions/similar experiences would be appreciated. Thank You.

The solution to every problem with excel
 

I'm not familiar with pivot tables, and in reading about ways to use SUMIF
with multiple criteria, I only found out about SUMPRODUCT. This is
essentially what I'm trying to do:

Columns A, B, and C make up a list of transactions, with the date in A, the
nature of the transaction in B, and the amount in C.
A B C
Date Transaction Amount
1/25/2007 Withdraw 100
1/30/2007 Deposit 100
etc.

I want, for example, the sum of all deposits between 2 dates. I could use

=DSUM(A1:C65000,"Amount",E1:G2)

Where E1:G2 is

E F G
Date Date Transaction
1/23/2007 <1/27/2007 ="=Deposit"

But in reality, I need, for each week, one of these for Withdraws, another
for Deposits, another for payments, three more for each of three categories
of charges, and probably some more, which would be a lot of work to set up,
and would need done every time you want to update the sheet for the next
duration of time.

I could also use

=SUMPRODUCT((A2:A11I1)*(A2:A11<J1)*(B2:B11="Depos it")*(C2:C11))

Where I1 equals some date and J1 equals some date, but this slows things
down when calculating cells.

Will pivot tables work? If so, I'll definately look into it. Thanks for
all the help, everyone!



"jasonc" wrote:

A few comments:
It would be helpful to know how many criteria fields you have. A better
choice may be =sumif or a pivot table. Sumproduct is not really a query tool
-- you can use it that way as an array function, but that's not really what
it's for. Also, you could use =sum as an array as well, but that get's tricky
too. The DSUM implementation in excel was poorly thought thru way back in
the early 90s, and so you're right that you wind up with a bunch of
repetitive criteria all over the place.
Jason



"Adam Graham" wrote:

Is to post your problem on here and continue searching for the answer on your
own. Once you post your question and help is on its way, you will inevitably
figure it out on your own within a matter of minutes.

I can just turn off automatic cell calculation under 'options' and hit F9 to
calculate my totals, which is what I'll probably do.

Nonetheless, a more efficient formula would improve my worksheet and
suggestions are still very much appreciated.


"Adam Graham" wrote:

I was using DSUM to sum financial transactions based on a variety of
criteria, many of which involved dates. The problem for this is that I would
have to make a variety of tables of DSUM criteria (I apologize if I'm not
using the right terminology here) for each week, so to set the worksheet up
for an entire year would be tedious and time consuming, not to mention have a
plethera of potential for careless errors.

And so, I created a new worksheet to do the same thing using SUMPRODUCT, and
while this worksheet has the advantage of being updatable for a new week
simply by dragging an existing cell into the one below it, after entering a
new transaction, the cursor freezes for several seconds and at the bottom of
the screen it says 'calculating cells xx%' which takes time to raise to 100%.


So, in short, I have two solutions to the problem: Using DSUM makes the
worksheet hard to update, and using SUMPRODUCT makes the worksheet hard to
use. Is it simply a matter of changing a feature to allow new input while
cells are being calculated? Why is SUMPRODUCT so inefficient? Is there
another, equally mobile solution that is more efficient?

Any suggestions/similar experiences would be appreciated. Thank You.

The solution to every problem with excel
 

Based on what you described, I go for SUMPRODUCT which is versatile and dynamic. About the speed, one expert's previous advice was "take a coffee break while SUMPRODUCT crunches the numbers" - something like that.

Wonder what the gurus think.

Epinn (who learned about SUMPRODUCT before SUM and SUMIF)

"Adam Graham" wrote in message ...
I'm not familiar with pivot tables, and in reading about ways to use SUMIF
with multiple criteria, I only found out about SUMPRODUCT. This is
essentially what I'm trying to do:

Columns A, B, and C make up a list of transactions, with the date in A, the
nature of the transaction in B, and the amount in C.
A B C
Date Transaction Amount
1/25/2007 Withdraw 100
1/30/2007 Deposit 100
etc.

I want, for example, the sum of all deposits between 2 dates. I could use

=DSUM(A1:C65000,"Amount",E1:G2)

Where E1:G2 is

E F G
Date Date Transaction
1/23/2007 <1/27/2007 ="=Deposit"

But in reality, I need, for each week, one of these for Withdraws, another
for Deposits, another for payments, three more for each of three categories
of charges, and probably some more, which would be a lot of work to set up,
and would need done every time you want to update the sheet for the next
duration of time.

I could also use

=SUMPRODUCT((A2:A11I1)*(A2:A11<J1)*(B2:B11="Depos it")*(C2:C11))

Where I1 equals some date and J1 equals some date, but this slows things
down when calculating cells.

Will pivot tables work? If so, I'll definately look into it. Thanks for
all the help, everyone!



"jasonc" wrote:

A few comments:
It would be helpful to know how many criteria fields you have. A better
choice may be =sumif or a pivot table. Sumproduct is not really a query tool
-- you can use it that way as an array function, but that's not really what
it's for. Also, you could use =sum as an array as well, but that get's tricky
too. The DSUM implementation in excel was poorly thought thru way back in
the early 90s, and so you're right that you wind up with a bunch of
repetitive criteria all over the place.
Jason



"Adam Graham" wrote:

Is to post your problem on here and continue searching for the answer on your
own. Once you post your question and help is on its way, you will inevitably
figure it out on your own within a matter of minutes.

I can just turn off automatic cell calculation under 'options' and hit F9 to
calculate my totals, which is what I'll probably do.

Nonetheless, a more efficient formula would improve my worksheet and
suggestions are still very much appreciated.


"Adam Graham" wrote:

I was using DSUM to sum financial transactions based on a variety of
criteria, many of which involved dates. The problem for this is that I would
have to make a variety of tables of DSUM criteria (I apologize if I'm not
using the right terminology here) for each week, so to set the worksheet up
for an entire year would be tedious and time consuming, not to mention have a
plethera of potential for careless errors.

And so, I created a new worksheet to do the same thing using SUMPRODUCT, and
while this worksheet has the advantage of being updatable for a new week
simply by dragging an existing cell into the one below it, after entering a
new transaction, the cursor freezes for several seconds and at the bottom of
the screen it says 'calculating cells xx%' which takes time to raise to 100%.


So, in short, I have two solutions to the problem: Using DSUM makes the
worksheet hard to update, and using SUMPRODUCT makes the worksheet hard to
use. Is it simply a matter of changing a feature to allow new input while
cells are being calculated? Why is SUMPRODUCT so inefficient? Is there
another, equally mobile solution that is more efficient?

Any suggestions/similar experiences would be appreciated. Thank You.

The solution to every problem with excel
 

I am very curious how much faster it will be using

(1) DataFilterAutoFilter and
(2) SUBTOTAL()

instead of SUMPRODUCT.

However, I may prefer Excel doing the work (SUMPRODUCT) to my setting up (AutoFilter) every time. You may still need to change the criteria in the SUMPRODUCT formulae every time?

I would appreciate any feedback on speed in terms of SUBTOTAL if you care to experiment.

Epinn

"Epinn" wrote in message ...
Based on what you described, I go for SUMPRODUCT which is versatile and dynamic. About the speed, one expert's previous advice was "take a coffee break while SUMPRODUCT crunches the numbers" - something like that.

Wonder what the gurus think.

Epinn (who learned about SUMPRODUCT before SUM and SUMIF)

"Adam Graham" wrote in message ...
I'm not familiar with pivot tables, and in reading about ways to use SUMIF
with multiple criteria, I only found out about SUMPRODUCT. This is
essentially what I'm trying to do:

Columns A, B, and C make up a list of transactions, with the date in A, the
nature of the transaction in B, and the amount in C.
A B C
Date Transaction Amount
1/25/2007 Withdraw 100
1/30/2007 Deposit 100
etc.

I want, for example, the sum of all deposits between 2 dates. I could use

=DSUM(A1:C65000,"Amount",E1:G2)

Where E1:G2 is

E F G
Date Date Transaction
1/23/2007 <1/27/2007 ="=Deposit"

But in reality, I need, for each week, one of these for Withdraws, another
for Deposits, another for payments, three more for each of three categories
of charges, and probably some more, which would be a lot of work to set up,
and would need done every time you want to update the sheet for the next
duration of time.

I could also use

=SUMPRODUCT((A2:A11I1)*(A2:A11<J1)*(B2:B11="Depos it")*(C2:C11))

Where I1 equals some date and J1 equals some date, but this slows things
down when calculating cells.

Will pivot tables work? If so, I'll definately look into it. Thanks for
all the help, everyone!



"jasonc" wrote:

A few comments:
It would be helpful to know how many criteria fields you have. A better
choice may be =sumif or a pivot table. Sumproduct is not really a query tool
-- you can use it that way as an array function, but that's not really what
it's for. Also, you could use =sum as an array as well, but that get's tricky
too. The DSUM implementation in excel was poorly thought thru way back in
the early 90s, and so you're right that you wind up with a bunch of
repetitive criteria all over the place.
Jason



"Adam Graham" wrote:

Is to post your problem on here and continue searching for the answer on your
own. Once you post your question and help is on its way, you will inevitably
figure it out on your own within a matter of minutes.

I can just turn off automatic cell calculation under 'options' and hit F9 to
calculate my totals, which is what I'll probably do.

Nonetheless, a more efficient formula would improve my worksheet and
suggestions are still very much appreciated.


"Adam Graham" wrote:

I was using DSUM to sum financial transactions based on a variety of
criteria, many of which involved dates. The problem for this is that I would
have to make a variety of tables of DSUM criteria (I apologize if I'm not
using the right terminology here) for each week, so to set the worksheet up
for an entire year would be tedious and time consuming, not to mention have a
plethera of potential for careless errors.

And so, I created a new worksheet to do the same thing using SUMPRODUCT, and
while this worksheet has the advantage of being updatable for a new week
simply by dragging an existing cell into the one below it, after entering a
new transaction, the cursor freezes for several seconds and at the bottom of
the screen it says 'calculating cells xx%' which takes time to raise to 100%.


So, in short, I have two solutions to the problem: Using DSUM makes the
worksheet hard to update, and using SUMPRODUCT makes the worksheet hard to
use. Is it simply a matter of changing a feature to allow new input while
cells are being calculated? Why is SUMPRODUCT so inefficient? Is there
another, equally mobile solution that is more efficient?

Any suggestions/similar experiences would be appreciated. Thank You.

SumProduct making things work slow
 

see http://msdn2.microsoft.com/en-us/library/aa730921.aspx

and check out my website

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Adam Graham" wrote in message
...
I was using DSUM to sum financial transactions based on a variety of
criteria, many of which involved dates. The problem for this is that I
would
have to make a variety of tables of DSUM criteria (I apologize if I'm not
using the right terminology here) for each week, so to set the worksheet
up
for an entire year would be tedious and time consuming, not to mention
have a
plethera of potential for careless errors.

And so, I created a new worksheet to do the same thing using SUMPRODUCT,
and
while this worksheet has the advantage of being updatable for a new week
simply by dragging an existing cell into the one below it, after entering
a
new transaction, the cursor freezes for several seconds and at the bottom
of
the screen it says 'calculating cells xx%' which takes time to raise to
100%.


So, in short, I have two solutions to the problem: Using DSUM makes the
worksheet hard to update, and using SUMPRODUCT makes the worksheet hard to
use. Is it simply a matter of changing a feature to allow new input while
cells are being calculated? Why is SUMPRODUCT so inefficient? Is there
another, equally mobile solution that is more efficient?

Any suggestions/similar experiences would be appreciated. Thank You.