Calculation Speed

[ColleenK]

I post this before and implemented the suggestions, to no avail, so I am
asking for further ideas.

This is Excel 2003 and I have a workbook with 15 tabs, each tab consists of
the following formulas: sumif, sumproduct, vlookup and embedded if
statements. This takes about 10 minutes to calculate and when I hit "save
as", I generally have to wait 15-20 minutes. My PC is a 200 GHz maching with
1 GB of Ram, does anyone have any suggestions as to how I can speed up the
calculations?

 

[Sean Timmons]

the calcs will take as long as they take... if the formulas are to external
workbooks, ensure they are open when you go to save.

Perhaps it's a matter of either separating these 15 tabs into several
workbooks or finding ways to use less formulas....

 

[Sean Timmons]

the calcs will take as long as they take... if the formulas are to external
workbooks, ensure they are open when you go to save.

Perhaps it's a matter of either separating these 15 tabs into several
workbooks or finding ways to use less formulas....

 

[Elkar]

In my experience, the SUMIF and COUNTIF functions are not very efficient for
calc times. I always use SUMPRODUCT instead. Plus, SUMPRODUCT is just more
flexible.

Try replacing all of your SUMIFs with SUMPRODUCT. I'm betting that will cut
down your calc time substantially.

HTH
Elkar

 

[Elkar]

In my experience, the SUMIF and COUNTIF functions are not very efficient for
calc times. I always use SUMPRODUCT instead. Plus, SUMPRODUCT is just more
flexible.

Try replacing all of your SUMIFs with SUMPRODUCT. I'm betting that will cut
down your calc time substantially.

HTH
Elkar

 

[ColleenK]

Thanks I will give that a try.
--
CK

In my experience, the SUMIF and COUNTIF functions are not very efficient for
calc times. I always use SUMPRODUCT instead. Plus, SUMPRODUCT is just more
flexible.

Try replacing all of your SUMIFs with SUMPRODUCT. I'm betting that will cut
down your calc time substantially.

HTH
Elkar

 

[ColleenK]

Thanks I will give that a try.
--
CK

In my experience, the SUMIF and COUNTIF functions are not very efficient for
calc times. I always use SUMPRODUCT instead. Plus, SUMPRODUCT is just more
flexible.

Try replacing all of your SUMIFs with SUMPRODUCT. I'm betting that will cut
down your calc time substantially.

HTH
Elkar

 

[Sean Timmons]

And an embedded if can be a bit disconcerting as well.. Dependong on how many
layers we're looking at.......

 

[Sean Timmons]

And an embedded if can be a bit disconcerting as well.. Dependong on how many
layers we're looking at.......

 

[T. Valko]

the SUMIF and COUNTIF functions are not very

efficient for calc times. I always use SUMPRODUCT instead.

Are you sure you don't have that backwards?

 

[T. Valko]

the SUMIF and COUNTIF functions are not very

efficient for calc times. I always use SUMPRODUCT instead.

Are you sure you don't have that backwards?

 

[Elkar]

I'm quite certain. Set up some test data and try it out for yourself. A
quick test using 2 columns of 65000 rows each, the SUMIF function noticeably
hangs for a few seconds while calculating. The SUMPRODUCT function is
virtually instant.

 

[Elkar]

I'm quite certain. Set up some test data and try it out for yourself. A
quick test using 2 columns of 65000 rows each, the SUMIF function noticeably
hangs for a few seconds while calculating. The SUMPRODUCT function is
virtually instant.

 

[T. Valko]

Hmmm...

That's interesting. I can assure you that a general SUMIF/COUNTIF formula is
significantly more efficeint than the equivalent SUMPRODUCT formula.

Here's what I did...

Filled A1:A65000 with random letters A or B.
Filled B1:B65000 with random numbers 0 to 100.

Using Charles Williams RangeTimer method to measure the calculation time:

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

Average of 5 calculations for each formula:

COUNTIF and the equivalent SUMPRODUCT

=COUNTIF(A:A,"A") = 0.007 secs
=SUMPRODUCT(--(A1:A65000="A")) = 0.046 secs
=SUMPRODUCT((A1:A65000="A")*1) = 0.053 secs

=COUNTIF(B:B,">=50")-COUNTIF(B:B,">75") = 0.011 secs
=SUMPRODUCT((B1:B65000>=50)*(B1:B65000<=75)) = 0.120 secs
=SUMPRODUCT(--(B1:B65000>=50),--(B1:B65000<=75)) = 0.123 secs

SUMIF and the equivalent SUMPRODUCT

=SUMIF(A:A,"A",B:B) = 0.009 secs
=SUMPRODUCT(--(A1:A65000="A"),B1:B65000) = 0.056 secs
=SUMPRODUCT((A1:A65000="A")*B1:B65000) = 0.059 secs

 

[T. Valko]

Hmmm...

That's interesting. I can assure you that a general SUMIF/COUNTIF formula is
significantly more efficeint than the equivalent SUMPRODUCT formula.

Here's what I did...

Filled A1:A65000 with random letters A or B.
Filled B1:B65000 with random numbers 0 to 100.

Using Charles Williams RangeTimer method to measure the calculation time:

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

Average of 5 calculations for each formula:

COUNTIF and the equivalent SUMPRODUCT

=COUNTIF(A:A,"A") = 0.007 secs
=SUMPRODUCT(--(A1:A65000="A")) = 0.046 secs
=SUMPRODUCT((A1:A65000="A")*1) = 0.053 secs

=COUNTIF(B:B,">=50")-COUNTIF(B:B,">75") = 0.011 secs
=SUMPRODUCT((B1:B65000>=50)*(B1:B65000<=75)) = 0.120 secs
=SUMPRODUCT(--(B1:B65000>=50),--(B1:B65000<=75)) = 0.123 secs

SUMIF and the equivalent SUMPRODUCT

=SUMIF(A:A,"A",B:B) = 0.009 secs
=SUMPRODUCT(--(A1:A65000="A"),B1:B65000) = 0.056 secs
=SUMPRODUCT((A1:A65000="A")*B1:B65000) = 0.059 secs

 

[Elkar]

Very interesting indeed. Using your examples, the calc time is better with
SUMIF. However, I tried filling column A with =ROW(), column B with
=RIGHT(A1,1) and Column C with =RAND().

In D1, I placed the formula: =SUMIF(B1:B65535,"2",C1:C65535)

It takes over 10 seconds to calculate.

I then placed an apostrophe in front of D1 to "disable" it. Then, in D2 I
placed the formula: =SUMPRODUCT((B1:B65535="2")*C1:C65535)

It takes less than 1 second.

Repeated the process with the same results.

So, I'm going to play around with this more, but apparently other factors
have influence on the performace of SUMIF and SUMPRODUCT.

 

[Elkar]

Very interesting indeed. Using your examples, the calc time is better with
SUMIF. However, I tried filling column A with =ROW(), column B with
=RIGHT(A1,1) and Column C with =RAND().

In D1, I placed the formula: =SUMIF(B1:B65535,"2",C1:C65535)

It takes over 10 seconds to calculate.

I then placed an apostrophe in front of D1 to "disable" it. Then, in D2 I
placed the formula: =SUMPRODUCT((B1:B65535="2")*C1:C65535)

It takes less than 1 second.

Repeated the process with the same results.

So, I'm going to play around with this more, but apparently other factors
have influence on the performace of SUMIF and SUMPRODUCT.

 

[T. Valko]

Now that is indeed interesting!

I don't know why that does that and I'll have to consult a real expert on
that one but I suspect (know) the RAND function is involved somehow. If you
convert column C to constants the SUMIF version is faster to calculate than
the SUMPRODUCT version.

Although the sample you describe does what it does, it's *not* because SUMIF
is less efficient than SUMPRODUCT. If you time just the SUMIF formula and
compare its calc time to just the SUMPRODUCT version you'll see that the
SUMIF version is faster to calculate, about 0.054 secs vs 0.058 secs. All
that other time is taken by the RAND() functions recalculating.

Let me see what I can find out.

 

[T. Valko]

Now that is indeed interesting!

I don't know why that does that and I'll have to consult a real expert on
that one but I suspect (know) the RAND function is involved somehow. If you
convert column C to constants the SUMIF version is faster to calculate than
the SUMPRODUCT version.

Although the sample you describe does what it does, it's *not* because SUMIF
is less efficient than SUMPRODUCT. If you time just the SUMIF formula and
compare its calc time to just the SUMPRODUCT version you'll see that the
SUMIF version is faster to calculate, about 0.054 secs vs 0.058 secs. All
that other time is taken by the RAND() functions recalculating.

Let me see what I can find out.

 

[Harlan Grove]

Now that is indeed interesting!

I don't know why that does that and I'll have to consult a real expert on
that one but I suspect (know) the RAND function is involved somehow. If you
convert column C to constants the SUMIF version is faster to calculate than
the SUMPRODUCT version.

....

Speculation, but it sure seems that SUMIF triggers recalc after
accessing each cell in its 1st argument. SUMIF and COUNTIF give the
same abysmal recalc performance against a large range filled with
formulas calling TODAY. If so, the rule is simple: use SUMIF and
COUNTIF on ranges containing constants or formulas calling no volatile
functions; use SUMPRODUCT on ranges containing formulas calling
volatile functions. When in doubt, use SUMPRODUCT because it'll be
slow by milliseconds when it's suboptimal. SUMIF and COUNTIF will be
slow by TENS OF SECONDS when they're suboptimal.