Sumproduct Formula Calculate Just Like Marathon Race

Hellow friends, I m using excel 2003.

I have Three sheet
FY0910
FY1011
SERIESWISE

I have data in Fy0910 & FY1011 sheet. In those sheet lot of data approx 20,000 row & nearabout 30 column.

In Fy0910 & Fy1011 : A column = invno, b column date, c column code, e column name, n column quantity, s column series, ab column month&date formula, ad column branch

Now i have calculate in serieswise sheet using sumproduct formula
and calculate data wise, series wise, customer code wise, also banch wise.

In Column F9 start date 01 Apr-09 to F374 row 31 mar 10 & H9 start date is 01 Apr 10 to H374 31 march 11. In column AD to AJ show as branch. In AD2 & AM2 type series, AD3 & AM3 customer code, AD7 to AJ7 branch of fy0910 & AM7 to AS7 branch of fy1011.

Now i calculate AD9 to AJ374 using sumproduct formula for fy0910 sheet calculation & AM9 to AS374 for fy1011 sheet calculate. I also used conditional formating if qty is > 1 display green & if qty is 0 then show as red colur.

My problem is when i need to change or edit series or select customer code, calculatation start very very very slow. It will take atleast 10 to 15 minutes.

Any other option to calcuate faster. Using other any option in same excel sheet or through vb. Please note that i m not familier with vb.

I have attach file for your ready reference.

When using SUMPRODUCT it is imperative you keep ranges as lean as possible.

If we take one formula:

=SUMPRODUCT((((('FY0910'!$B$2:$B$65536=$F9)*('FY0910'!$AB$2:$AB$65536=$G9)*('FY0910'!$AD$2:$AD$65536=AD$7)*('FY0910'!$S$2:$S$65536=$AD$2)*('FY0910'!$C$2:$C$65536=$AD$3)*('FY0910'!$N$2:$N$65536)))))

From the above we can see:

a) you're referencing 65535 cells in each range
b) you have 6 ranges

This means you're referencing 65535 * 6 -> 393,210 cells in any given SUMPRODUCT (ignoring criteria)

If you then consider that you have thousands of these formulae it means you're looking at literally millions of cells.

Not surprising therefore that performance is pretty hideous (you also have lots of Conditional Formatting which is super-volatile)

Given use of XL2003 I would suggest you first look to avoid the need for SUMPRODUCT altogether... IMO SUMPRODUCT should be used in moderation.

First thing therefore would be to create a Key of all the key pieces of info. on each row in FY0910 & FY1011 sheets, eg:

Please Login or Register  to view this content.

With this key in place you can now dispense with SUMPRODUCT and use the much more efficient SUMIF function on your SERIESWISE sheet such that:

Please Login or Register  to view this content.

In terms of actual comparison... if we look only at calculating the matrix AD9:AJ374:

-- SUMPRODUCT approach took on average 80 seconds to calculate

-- SUMIF utilising "key" column on FY0910/FY1011 sheets took on average 0.02 seconds to calculate

For the sake of demo. see attached which has the above in place

You will note the file is running on Auto Calculation and will calculate instantaneously should you change any of the "variables" on SERIESWISE sheet

edit: per the note at MrExcel.com - if you opt to x-post please have the courtesy to detail all relevant links:

http://www.mrexcel.com/forum/showthread.php?p=2411003