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?
--
CK

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

"ColleenK" wrote:

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?
--
CK

"Sean Timmons" wrote in message
...
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....

"ColleenK" wrote:

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?
--
CK

CK wrote <<Perhaps it's a matter of either separating these 15 tabs into
several workbooks A downside of this is that any structural changes in a
workbook will not be referred to correctly in another workbook and any
recalculation will be incorrect. Move just one cell and everything could go
to pot!

Bill Ridgeway.

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" wrote:

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?
--
CK

Thanks I will give that a try.
--
CK


"Elkar" wrote:

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" wrote:

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?
--
CK

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

"ColleenK" wrote:

Thanks I will give that a try.
--
CK


"Elkar" wrote:

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" wrote:

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?
--
CK

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?

--
Biff
Microsoft Excel MVP


"Elkar" wrote in message
...
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" wrote:

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?
--
CK

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" wrote:

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?

--
Biff
Microsoft Excel MVP


"Elkar" wrote in message
...
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" wrote:

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?
--
CK

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


--
Biff
Microsoft Excel MVP


"Elkar" wrote in message
...
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" wrote:

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?

--
Biff
Microsoft Excel MVP


"Elkar" wrote in message
...
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" wrote:

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?
--
CK

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" wrote:

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


--
Biff
Microsoft Excel MVP


"Elkar" wrote in message
...
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" wrote:

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?

--
Biff
Microsoft Excel MVP


"Elkar" wrote in message
...
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" wrote:

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?
--
CK