I have developed a spreadsheet on my home computer which does a lot of
SUMPRODUCT calculating (I know this can be a slow calculating function). On
my computer the spreadsheet takes 2 minutes to do its calculating. I
transferred the speadsheet to the company I was doing work for & run the same
spreadsheet but it takes 40 minutes to do it's calculating. This was running
over a network so I tried to run on the local drive on the fastest PC there
(it is only a slightly lower spec than my machine) & it still takes 30
minutes.

The SUMPRODUCT looks up a worksheet table which Excel gets thru a query. I
have only the minimum number of lines in the table that it needs as I know
the SUMPRODUCT function takes alot more time on larger sized tables. I have
tried turning off any services that the PC was running, such as antivirus,
that could slow the PC down but it didn't make any difference. What things
should I look for to make this faster?

Below is the type of code I have in the spreadsheet.

=SUMPRODUCT((Sheet2!A$1:A$3110="Family")*(LEFT(She et2!F$1:F$3110,20)="Counselling
- family")*(Sheet2!B$1:B$3110<"
")*(LEFT(Sheet2!D$1:D$3110,1)="A")*(Sheet2!E$1:E$3 110=A12)*(Sheet2!E$1:E$3110<A13)*(Sheet2!C$1:C$31 10=C$6))

Is there another function that I could use other thatn the SUMPRODUCT
function? TIA
--
Tony

Hi Tony,

Difficult to say from a distance.

But do visit Charles Williams' site:

www.decisionmodels.com

BTW it wouldn't surprise me if memory shortage is a problem. Any signs of excessive paging in the Task manager?

Personally I prefer to have helper columns with intermediate results; easy to check for correctness and usually rather fast,
faster than SUMPRODUCT anyway.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"TonyL" wrote in message ...
|I have developed a spreadsheet on my home computer which does a lot of
| SUMPRODUCT calculating (I know this can be a slow calculating function). On
| my computer the spreadsheet takes 2 minutes to do its calculating. I
| transferred the speadsheet to the company I was doing work for & run the same
| spreadsheet but it takes 40 minutes to do it's calculating. This was running
| over a network so I tried to run on the local drive on the fastest PC there
| (it is only a slightly lower spec than my machine) & it still takes 30
| minutes.
|
| The SUMPRODUCT looks up a worksheet table which Excel gets thru a query. I
| have only the minimum number of lines in the table that it needs as I know
| the SUMPRODUCT function takes alot more time on larger sized tables. I have
| tried turning off any services that the PC was running, such as antivirus,
| that could slow the PC down but it didn't make any difference. What things
| should I look for to make this faster?
|
| Below is the type of code I have in the spreadsheet.
|
| =SUMPRODUCT((Sheet2!A$1:A$3110="Family")*(LEFT(She et2!F$1:F$3110,20)="Counselling
| - family")*(Sheet2!B$1:B$3110<"
| ")*(LEFT(Sheet2!D$1:D$3110,1)="A")*(Sheet2!E$1:E$3 110=A12)*(Sheet2!E$1:E$3110<A13)*(Sheet2!C$1:C$31 10=C$6))
|
| Is there another function that I could use other thatn the SUMPRODUCT
| function? TIA
| --
| Tony

Thanks Niek. I will have a look at the website.

Yes, Excel on the PC at the company is using 100% CPU usage. PC is 2.66 GHz
with 740MB RAM

Mine is a 1.8 GHz dual core with 1GB RAM tablet & when it is doing the
spreadsheet it is only using about 50% CPU
--
Tony


"Niek Otten" wrote:

Hi Tony,

Difficult to say from a distance.

But do visit Charles Williams' site:

www.decisionmodels.com

BTW it wouldn't surprise me if memory shortage is a problem. Any signs of excessive paging in the Task manager?

Personally I prefer to have helper columns with intermediate results; easy to check for correctness and usually rather fast,
faster than SUMPRODUCT anyway.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"TonyL" wrote in message ...
|I have developed a spreadsheet on my home computer which does a lot of
| SUMPRODUCT calculating (I know this can be a slow calculating function). On
| my computer the spreadsheet takes 2 minutes to do its calculating. I
| transferred the speadsheet to the company I was doing work for & run the same
| spreadsheet but it takes 40 minutes to do it's calculating. This was running
| over a network so I tried to run on the local drive on the fastest PC there
| (it is only a slightly lower spec than my machine) & it still takes 30
| minutes.
|
| The SUMPRODUCT looks up a worksheet table which Excel gets thru a query. I
| have only the minimum number of lines in the table that it needs as I know
| the SUMPRODUCT function takes alot more time on larger sized tables. I have
| tried turning off any services that the PC was running, such as antivirus,
| that could slow the PC down but it didn't make any difference. What things
| should I look for to make this faster?
|
| Below is the type of code I have in the spreadsheet.
|
| =SUMPRODUCT((Sheet2!A$1:A$3110="Family")*(LEFT(She et2!F$1:F$3110,20)="Counselling
| - family")*(Sheet2!B$1:B$3110<"
| ")*(LEFT(Sheet2!D$1:D$3110,1)="A")*(Sheet2!E$1:E$3 110=A12)*(Sheet2!E$1:E$3110<A13)*(Sheet2!C$1:C$31 10=C$6))
|
| Is there another function that I could use other thatn the SUMPRODUCT
| function? TIA
| --
| Tony

Hi Tony,

I'm not very good at this, but I think you get 50% because only one of the processors is used. I did notice that modern processors
(like yours) are a lot faster than older ones. The GHz does not count too much then.


"TonyL" wrote in message ...
| Thanks Niek. I will have a look at the website.
|
| Yes, Excel on the PC at the company is using 100% CPU usage. PC is 2.66 GHz
| with 740MB RAM
|
| Mine is a 1.8 GHz dual core with 1GB RAM tablet & when it is doing the
| spreadsheet it is only using about 50% CPU
| --
| Tony
|
|
| "Niek Otten" wrote:
|
| Hi Tony,
|
| Difficult to say from a distance.
|
| But do visit Charles Williams' site:
|
| www.decisionmodels.com
|
| BTW it wouldn't surprise me if memory shortage is a problem. Any signs of excessive paging in the Task manager?
|
| Personally I prefer to have helper columns with intermediate results; easy to check for correctness and usually rather fast,
| faster than SUMPRODUCT anyway.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
| "TonyL" wrote in message ...
| |I have developed a spreadsheet on my home computer which does a lot of
| | SUMPRODUCT calculating (I know this can be a slow calculating function). On
| | my computer the spreadsheet takes 2 minutes to do its calculating. I
| | transferred the speadsheet to the company I was doing work for & run the same
| | spreadsheet but it takes 40 minutes to do it's calculating. This was running
| | over a network so I tried to run on the local drive on the fastest PC there
| | (it is only a slightly lower spec than my machine) & it still takes 30
| | minutes.
| |
| | The SUMPRODUCT looks up a worksheet table which Excel gets thru a query. I
| | have only the minimum number of lines in the table that it needs as I know
| | the SUMPRODUCT function takes alot more time on larger sized tables. I have
| | tried turning off any services that the PC was running, such as antivirus,
| | that could slow the PC down but it didn't make any difference. What things
| | should I look for to make this faster?
| |
| | Below is the type of code I have in the spreadsheet.
| |
| | =SUMPRODUCT((Sheet2!A$1:A$3110="Family")*(LEFT(She et2!F$1:F$3110,20)="Counselling
| | - family")*(Sheet2!B$1:B$3110<"
| | ")*(LEFT(Sheet2!D$1:D$3110,1)="A")*(Sheet2!E$1:E$3 110=A12)*(Sheet2!E$1:E$3110<A13)*(Sheet2!C$1:C$31 10=C$6))
| |
| | Is there another function that I could use other thatn the SUMPRODUCT
| | function? TIA
| | --
| | Tony
|
|
|