Sumproduct Formula

[Mike]

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...

 

[Alan]

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.

 

[David Billigmeier]

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

 

[Aladin Akyurek]

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:



=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

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.

 

[RagDyer]

Aladin,
Would you comment on the perceived advantage of using
+0
Instead of the asterisk or the unary.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

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:



=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

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.

 

[Ashish Mathur]

Hi Mike,

An effectivre alternative to using the sumproduct formula could be the
Database functions of Excel i.e. functions like DSUM, DGET, DPRODUCT etc.
You will get extensive help in the HELP Menu.

Hope this information helps.

Regards,

 

[Aladin Akyurek]

Performancewise, +0 is very close to --, the double negation, both
better than other coercers. Invoking one as the other is a "weak"
attempt on my part to emphasize the real thing, that is, the comma
syntax, which is confounded by far too many with the coercer issue. All
this will subside when SumProduct does the coercion itself. See [1] in
my signature, which is a SumProduct-specific proposal.

Aladin,
Would you comment on the perceived advantage of using
+0
Instead of the asterisk or the unary.

--

[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.

 

[RagDyeR]

Thanks for your answer Aladin.

This is the second time in as many days that I'm bringing up *my* issue with
the unary, or as you mention here, the "comma" syntax.

In today's world, the use of the web for mining data is as common a fact of
life as is the use of the computer itself.
They are in fact, virtually synonymous.

Data imported into XL, and the form and format of this data, is a very
common issue within these NGs.
On a daily basis, there are numerous questions pertaining to unworkable
formulas, where the ultimate solution is to "homogenize" the data forms and
formats.

The "problem" with the comma syntax is, it's *sneaky*!
In a convoluted scenario of "mixed" data, it returns a "wrong" result,
without any conspicuous declaration.
Zero is calculated for the "bad" data (numeric text as well as alpha text),
and its result is mixed in with the "good" data.

The developer completes the project and it's turned over to office staff for
implementation.
Then, let the cards fall where they may!

The asterisk form, on the other hand, *does* calculate the numeric text, and
"errors out" in the presence of alpha text, thus performing double duty.
It's the notification that's the important thing.
If you're told something's wrong, you can look for it!

There are cases where the comma form is necessary, but that's a mute point
in this discourse.

I'll get off my soap box now.<g>
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------



Performancewise, +0 is very close to --, the double negation, both
better than other coercers. Invoking one as the other is a "weak"
attempt on my part to emphasize the real thing, that is, the comma
syntax, which is confounded by far too many with the coercer issue. All
this will subside when SumProduct does the coercion itself. See [1] in
my signature, which is a SumProduct-specific proposal.

Aladin,
Would you comment on the perceived advantage of using
+0
Instead of the asterisk or the unary.

--

[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.

 

[Aladin Akyurek]

RagDyeR wrote:

[...]

This is the second time in as many days that I'm bringing up *my* issue with
the unary, or as you mention here, the "comma" syntax.

To clear up any misunderstanding, I'm definitely in favor of the
comma-syntax, while you are, in fact, arguing/recommending against the
use of SumProduct's comma-syntax.

In today's world, the use of the web for mining data is as common a fact of
life as is the use of the computer itself.
They are in fact, virtually synonymous.

I don't think so. That was the point of my reply.

Data imported into XL, and the form and format of this data, is a very
common issue within these NGs.
On a daily basis, there are numerous questions pertaining to unworkable
formulas, where the ultimate solution is to "homogenize" the data forms and
formats.

Issues with numeric data, erroneously typed as text (either by user or
by the "vagaries" of the system's parser) do not constitute a valid
reason to delegate the re-solution to functions.

The "problem" with the comma syntax is, it's *sneaky*!
In a convoluted scenario of "mixed" data, it returns a "wrong" result,
without any conspicuous declaration.
Zero is calculated for the "bad" data (numeric text as well as alpha text),
and its result is mixed in with the "good" data.

Such concerns are better dealt with by means of separate formulas that
audit the data. If a range should be numeric, a simple audit formula can
verify whether that is the case. For example:

=COUNT(Range)=ROWS(Range)

As a side note, I teach this subject in my audit classes with the 3rd
year accountancy students.

The developer completes the project and it's turned over to office staff for
implementation.
Then, let the cards fall where they may!

The developer should provide an audit sheet (rarely done), regarding the
data types and the processing a spreadsheet model carries out. Auditors
(e.g., accountants) ought to require audit sheets.

The asterisk form, on the other hand, *does* calculate the numeric text, and
"errors out" in the presence of alpha text, thus performing double duty.
It's the notification that's the important thing.

A data area can consist of either user-entered values or calculated
values. There might be good reasons for using ="" or any other
text-value. Such an area becomes unprocessible by your suggestion.

If you're told something's wrong, you can look for it!

Quite so. One would be well-advised to inspect the results of
judiciously set up audit formulas.

[...]

--

[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.

 

[Ragdyer]

<<<"To clear up any misunderstanding, I'm definitely in favor of the
comma-syntax, while you are, in fact, arguing/recommending against the
use of SumProduct's comma-syntax.">>>

No mis-understanding whatever, and you're absolutely correct in interpreting
my sentiments, as I correctly interpret yours.

"In today's world, the use of the web for mining data is as common a fact of
life as is the use of the computer itself.
They are in fact, virtually synonymous."

<<"I don't think so. That was the point of my reply.">>

I believe you're very wrong here, but since I'm not really in "the
Business", I can only speak from my knowledge of my industry.

We're a dying (no pun intended) breed (textile processing), along with shoe
manufacturing and garment manufacturing, and really can't compete with the
Asian hemisphere of the Pacific Rim. Textile processing doesn't move
without some connection to web based data and programs, in an attempt to cut
costs to the bone.
When I started in this business in 1956, 2 to 3 times a year a price book
and a color sample book would be mailed out gratis, to every mill in the
country, and in those days, that numbered literally in the tens of hundreds.

Today, they are non-existent, just as computer hardware and software
manuals.

<<"Issues with numeric data, erroneously typed as text (either by user or
by the "vagaries" of the system's parser) do not constitute a valid
reason to delegate the re-solution to functions.">>

This is the crux of the discussion.
WHAT "delegate the re-solution to functions"???
Nobody is re-inventing the wheel!

We all saw the "birth" of the SumProduct "revolution".
It started with the asterisk!
And it did a job.
Now, the "chick" form is the comma syntax, which *doesn't* do the same
"good" job.

In a 1,000 or 2,000 or 3,000 line sheet, who can perceive any difference in
"efficiency".
And really, WHO CARES, when the insurance is really un-debatable?

<<"Such concerns are better dealt with by means of separate formulas that
audit the data. If a range should be numeric, a simple audit formula can
verify whether that is the case.">>

We talk about efficiency on one hand, and then talk about adding more
function calls on the other.
Why use additional formulas when one can do the job?
Tell me that's not ludicrous!

<<"The developer should provide an audit sheet (rarely done), regarding the
data types and the processing a spreadsheet model carries out. Auditors
(e.g., accountants) ought to require audit sheets.">>

This means absolutely nothing to an office staff whose job it is to update
(revise, populate) the data list as often and as quickly as possible before
the 5:00 o'clock bell.

<<"A data area can consist of either user-entered values or calculated
values. There might be good reasons for using ="" or any other
text-value. Such an area becomes unprocessible by your suggestion.">>

Cosmetics aside, =0 is just as acceptable in most cases.

"If you're told something's wrong, you can look for it!"

<<"Quite so. One would be well-advised to inspect the results of
judiciously set up audit formulas.">>

Again, we address the need of additional function calls to check on
something that really *needs no* checking!

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

RagDyeR wrote:

[...]

This is the second time in as many days that I'm bringing up *my* issue with
the unary, or as you mention here, the "comma" syntax.

To clear up any misunderstanding, I'm definitely in favor of the
comma-syntax, while you are, in fact, arguing/recommending against the
use of SumProduct's comma-syntax.

In today's world, the use of the web for mining data is as common a fact of
life as is the use of the computer itself.
They are in fact, virtually synonymous.

I don't think so. That was the point of my reply.

Data imported into XL, and the form and format of this data, is a very
common issue within these NGs.
On a daily basis, there are numerous questions pertaining to unworkable
formulas, where the ultimate solution is to "homogenize" the data forms and
formats.

Issues with numeric data, erroneously typed as text (either by user or
by the "vagaries" of the system's parser) do not constitute a valid
reason to delegate the re-solution to functions.

The "problem" with the comma syntax is, it's *sneaky*!
In a convoluted scenario of "mixed" data, it returns a "wrong" result,
without any conspicuous declaration.
Zero is calculated for the "bad" data (numeric text as well as alpha text),
and its result is mixed in with the "good" data.

Such concerns are better dealt with by means of separate formulas that
audit the data. If a range should be numeric, a simple audit formula can
verify whether that is the case. For example:

=COUNT(Range)=ROWS(Range)

As a side note, I teach this subject in my audit classes with the 3rd
year accountancy students.

The developer completes the project and it's turned over to office staff for
implementation.
Then, let the cards fall where they may!

The developer should provide an audit sheet (rarely done), regarding the
data types and the processing a spreadsheet model carries out. Auditors
(e.g., accountants) ought to require audit sheets.

The asterisk form, on the other hand, *does* calculate the numeric text, and
"errors out" in the presence of alpha text, thus performing double duty.
It's the notification that's the important thing.

A data area can consist of either user-entered values or calculated
values. There might be good reasons for using ="" or any other
text-value. Such an area becomes unprocessible by your suggestion.

If you're told something's wrong, you can look for it!

Quite so. One would be well-advised to inspect the results of
judiciously set up audit formulas.

[...]

--

[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.

 

[Aladin Akyurek]

<<<"To clear up any misunderstanding, I'm definitely in favor of the
comma-syntax, while you are, in fact, arguing/recommending against the
use of SumProduct's comma-syntax.">>>

No mis-understanding whatever, and you're absolutely correct in interpreting
my sentiments, as I correctly interpret yours.

Good.

"In today's world, the use of the web for mining data is as common a fact of
life as is the use of the computer itself.
They are in fact, virtually synonymous."

<<"I don't think so. That was the point of my reply.">>

I believe you're very wrong here, but since I'm not really in "the
Business", I can only speak from my knowledge of my industry.

We're a dying (no pun intended) breed (textile processing), along with shoe
manufacturing and garment manufacturing, and really can't compete with the
Asian hemisphere of the Pacific Rim. Textile processing doesn't move
without some connection to web based data and programs, in an attempt to cut
costs to the bone.
When I started in this business in 1956, 2 to 3 times a year a price book
and a color sample book would be mailed out gratis, to every mill in the
country, and in those days, that numbered literally in the tens of hundreds.

Today, they are non-existent, just as computer hardware and software
manuals.

Going back to "my reply," I insisteed that the comma-syntax should not
be confounded with any particular coercer. I'm not sure whether it has
anything to do with any business. Maybe are you trying to emphasize the
vagaries of "web-based input."

<<"Issues with numeric data, erroneously typed as text (either by user or
by the "vagaries" of the system's parser) do not constitute a valid
reason to delegate the re-solution to functions.">>

This is the crux of the discussion.
WHAT "delegate the re-solution to functions"???
Nobody is re-inventing the wheel!

We all saw the "birth" of the SumProduct "revolution".
It started with the asterisk!

Probably because it has been fashioned after formulas like:

{=SUM((X=x)*(Y=y)*Z)}

==>

=SUMPRODUCT((X=x)*(Y=y)*Z)

And it did a job.
Now, the "chick" form is the comma syntax, which *doesn't* do the same
"good" job.

Why not, given that

=SUMPRODUCT(--(A2:A7="a"),--(B2:B7="b"),--C2:C7)

is identical to a SumProduct formula set up with the non-comma syntax;

=SUMPRODUCT((A2:A7="a")*(B2:B7="b")*C2:C7)

By the way: Results of the SumProduct formulas with non-comma syntax
won't agree with those of the SumIf formulas, applied to the same range
to sum. Hope this isn't one of those mysterious statements.

In a 1,000 or 2,000 or 3,000 line sheet, who can perceive any difference in
"efficiency".
And really, WHO CARES, when the insurance is really un-debatable?

I do.

<<"Such concerns are better dealt with by means of separate formulas that
audit the data. If a range should be numeric, a simple audit formula can
verify whether that is the case.">>

We talk about efficiency on one hand, and then talk about adding more
function calls on the other.
Why use additional formulas when one can do the job?
Tell me that's not ludicrous!

You must be thinking of wrapping a calculation inside a complex IF.
That's not what I had in mind. An example:

B2 in Audit sheet reads 1, calculated by a formula that tests whether a
certain range, say X on sheet Y, consists of distinct items, a
requirement for further processing of X. Lets say that 1 means:
Condition is met. You have 2 choices:

(a) Check B2. If 0, take action regarding X.
(b) Wrap all formulas that process X, where appropriate, inside an IF
testing Audit!$B$2=1.

I think (a) is a sensible thing to do, while (b) can be resorted to if
it doesn't hurt efficiency too adversely.

<<"The developer should provide an audit sheet (rarely done), regarding the
data types and the processing a spreadsheet model carries out. Auditors
(e.g., accountants) ought to require audit sheets.">>

This means absolutely nothing to an office staff whose job it is to update
(revise, populate) the data list as often and as quickly as possible before
the 5:00 o'clock bell.

Maybe not. Even small and "quick and dirty" sheets would profit of
having an audit sheet.

<<"A data area can consist of either user-entered values or calculated
values. There might be good reasons for using ="" or any other
text-value. Such an area becomes unprocessible by your suggestion.">>

Cosmetics aside, =0 is just as acceptable in most cases.

If the issue is with erroneously text-type data and you're confident
about the error, the comma-syntax does the job too (See above).

"If you're told something's wrong, you can look for it!"

<<"Quite so. One would be well-advised to inspect the results of
judiciously set up audit formulas.">>

Again, we address the need of additional function calls to check on
something that really *needs no* checking!

When that confident and you need a SumProduct formula...

=SUMPRODUCT(--(X=x),--(Y=y),...,--Z)

or, if you dislike double negation...

=SUMPRODUCT((X=x)+0,(Y=y)+0,...,Z+0)

Note that, if SumProduct would coerce truth values implicitly, you'd have:

=SUMPRODUCT((X=x),(Y=y),...,Z+0)


--

[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.

 

[RagDyeR]

I guess we could go back and forth several more times, and I'm sure that
we'll both be unswerved from our own convictions.

You do your "thing", and I'll do mine.<g>
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

<<<"To clear up any misunderstanding, I'm definitely in favor of the
comma-syntax, while you are, in fact, arguing/recommending against the
use of SumProduct's comma-syntax.">>>

No mis-understanding whatever, and you're absolutely correct in interpreting
my sentiments, as I correctly interpret yours.

Good.

"In today's world, the use of the web for mining data is as common a fact of
life as is the use of the computer itself.
They are in fact, virtually synonymous."

<<"I don't think so. That was the point of my reply.">>

I believe you're very wrong here, but since I'm not really in "the
Business", I can only speak from my knowledge of my industry.

We're a dying (no pun intended) breed (textile processing), along with shoe
manufacturing and garment manufacturing, and really can't compete with the
Asian hemisphere of the Pacific Rim. Textile processing doesn't move
without some connection to web based data and programs, in an attempt to cut
costs to the bone.
When I started in this business in 1956, 2 to 3 times a year a price book
and a color sample book would be mailed out gratis, to every mill in the
country, and in those days, that numbered literally in the tens of hundreds.

Today, they are non-existent, just as computer hardware and software
manuals.

Going back to "my reply," I insisteed that the comma-syntax should not
be confounded with any particular coercer. I'm not sure whether it has
anything to do with any business. Maybe are you trying to emphasize the
vagaries of "web-based input."

<<"Issues with numeric data, erroneously typed as text (either by user or
by the "vagaries" of the system's parser) do not constitute a valid
reason to delegate the re-solution to functions.">>

This is the crux of the discussion.
WHAT "delegate the re-solution to functions"???
Nobody is re-inventing the wheel!

We all saw the "birth" of the SumProduct "revolution".
It started with the asterisk!

Probably because it has been fashioned after formulas like:

{=SUM((X=x)*(Y=y)*Z)}

==>

=SUMPRODUCT((X=x)*(Y=y)*Z)

And it did a job.
Now, the "chick" form is the comma syntax, which *doesn't* do the same
"good" job.

Why not, given that

=SUMPRODUCT(--(A2:A7="a"),--(B2:B7="b"),--C2:C7)

is identical to a SumProduct formula set up with the non-comma syntax;

=SUMPRODUCT((A2:A7="a")*(B2:B7="b")*C2:C7)

By the way: Results of the SumProduct formulas with non-comma syntax
won't agree with those of the SumIf formulas, applied to the same range
to sum. Hope this isn't one of those mysterious statements.

In a 1,000 or 2,000 or 3,000 line sheet, who can perceive any difference in
"efficiency".
And really, WHO CARES, when the insurance is really un-debatable?

I do.

<<"Such concerns are better dealt with by means of separate formulas that
audit the data. If a range should be numeric, a simple audit formula can
verify whether that is the case.">>

We talk about efficiency on one hand, and then talk about adding more
function calls on the other.
Why use additional formulas when one can do the job?
Tell me that's not ludicrous!

You must be thinking of wrapping a calculation inside a complex IF.
That's not what I had in mind. An example:

B2 in Audit sheet reads 1, calculated by a formula that tests whether a
certain range, say X on sheet Y, consists of distinct items, a
requirement for further processing of X. Lets say that 1 means:
Condition is met. You have 2 choices:

(a) Check B2. If 0, take action regarding X.
(b) Wrap all formulas that process X, where appropriate, inside an IF
testing Audit!$B$2=1.

I think (a) is a sensible thing to do, while (b) can be resorted to if
it doesn't hurt efficiency too adversely.

<<"The developer should provide an audit sheet (rarely done), regarding the
data types and the processing a spreadsheet model carries out. Auditors
(e.g., accountants) ought to require audit sheets.">>

This means absolutely nothing to an office staff whose job it is to update
(revise, populate) the data list as often and as quickly as possible before
the 5:00 o'clock bell.

Maybe not. Even small and "quick and dirty" sheets would profit of
having an audit sheet.

<<"A data area can consist of either user-entered values or calculated
values. There might be good reasons for using ="" or any other
text-value. Such an area becomes unprocessible by your suggestion.">>

Cosmetics aside, =0 is just as acceptable in most cases.

If the issue is with erroneously text-type data and you're confident
about the error, the comma-syntax does the job too (See above).

"If you're told something's wrong, you can look for it!"

<<"Quite so. One would be well-advised to inspect the results of
judiciously set up audit formulas.">>

Again, we address the need of additional function calls to check on
something that really *needs no* checking!

When that confident and you need a SumProduct formula...

=SUMPRODUCT(--(X=x),--(Y=y),...,--Z)

or, if you dislike double negation...

=SUMPRODUCT((X=x)+0,(Y=y)+0,...,Z+0)

Note that, if SumProduct would coerce truth values implicitly, you'd have:

=SUMPRODUCT((X=x),(Y=y),...,Z+0)


--

[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.