Shorten formula to avoid maxing of CPU??

**constructionsheets** · 08-19-2011, 11:16 PM

I would love to know if this formula can be shortened and if so will it stop the maxxing of my computers CPU when i enter data?I have an excel spreadsheet and the sheets are Preliminaries,Baseworks,Frame,Enclosed,Fixing,Practical Completion, The below formula allows me to in each of these sheets select a supplier for a product from a drop down menu and when the supplier is entered into the target cell (A42 in this case). I then have a list of suppliers and this formula next to each, allowing me to create a list of how much will be spent at each supplier on this project.

=SUM(IF(Preliminaries!C:C=A42,Preliminaries!G:G,0))+SUM(IF(Baseworks!C:C=A42,Baseworks!G:G,0))+SUM(I F(Frame!C:C=A42,Frame!G:G,0))+SUM(IF('Enclosed '!C:C=A42,'Enclosed '!G:G,0))+SUM(IF(Fixing!C:C=A42,Fixing!G:G,0))+SUM(IF('Practical Completion'!C:C=A42,'Practical Completion'!G:G,0))

I have tried

=SUM(IF(Preliminaries:Baseworks!C:C=A43,Preliminaries:Baseworks!G:G,0))

and it wont work. Is this formuloa shotenable and will it reduce the CPU eat up or is that just the fact that there is in excess of 150 rows of data in each sheet?

Thanks in advance.

**MarvinP** · 08-20-2011, 01:19 AM

Hi constructionssheets and welcome to the forum,

When you use the entire column in a formula it takes longer. In your formulas you have "C:C" and "G:G" asking about the entire column.

I'd try to shorten this range. Instead of "C:C" try using "C1:C100" (thinking there will always be less than 100 rows in your data). Do the same for all whole column ranges and see if that speeds things up.

I hope it does.

**JBeaucaire** · 08-20-2011, 02:18 AM

Well, shortening up the formula is possible... I created a list of your included sheets, gave the range of cells highlighted a "name" of MySheets (See Insert > Name > Define), then used a 3D SUMIF() formula in place of yours...

=SUM(SUMIF(INDIRECT("'" & MySheets & "'!C1:C300"), "=" & A2, INDIRECT("'" & MySheets & "'!G1:G300")))

...confirmed with CTRL-SHIFT-ENTER

This may be faster because I'm only using 300 rows, not a million, per sheet.

EDIT: This sat unposted for over an hour on my computer, sorry for the delay. Hat-tip to Marvin...

**DonkeyOte** · 08-20-2011, 02:41 AM

Reducing the Precedent Range is only really relevant if you need to use an Array which in this instance you don't.

=SUMIF(Preliminaries!C:C,A42,Preliminaries!G:G)+SUMIF(Baseworks!C:C,A42,Baseworks!G:G)+SUMIF(Frame!C:C,A42,Frame!G:G)+SUMIF('Enclosed '!C:C,A42,'Enclosed '!G:G)+SUMIF(Fixing!C:C,A42,Fixing!G:G)+SUMIF('Practical Completion'!C:C,A42,'Practical Completion'!G:G)

The length of a formula is not directly related to it's subsequent performance. As JB would concede the 3D SUMIF approach though "shorter" is less efficient than the above (and Volatile)