Sumproduct - Alternative

**Harlequin** · 08-09-2007, 12:03 PM

Hello.

I have a template that looks at for 4 variables from a data sheet (See Attached)

At the moment I’m using SUMPRODUCT, which works, but unfortunately when reading 50,000+ lines it becomes so slow to calculate all the data, especially since there is more than 1 location.

Is there another formula that can give the same result?, or code that can do it really quickly?

Cheers

**Paul** · 08-09-2007, 12:47 PM

Due to the way your data sheet is setup, neat columns and rows of like data, a Pivot Table would be far more efficient than using 200 SUMPRODUCT formulas over 50,000+ rows.

Review the attached sheet. I deleted your Template sheet with all the formulas and created a Sheet1, which has a PivotTable on it. I also duplicated your data so there were about 60,000 rows to test the speed. (To upload the revised sheet I had to delete most of the rows, though.)

All you would have to do when adding/removing data from the Data sheet is go to the pivot table sheet, click anywhere inside the pivot table and then click Data -> Refresh Data. It will update your numbers within a few seconds at most.

If you have multiple locations, dates, types, etc. you would simply just use the pivot table to filter your data. The grand totals rows can be set to count the data (# of entries), sum the data, average, etc..

Hopefully that will work for you.

**Ron Coderre** · 08-09-2007, 12:52 PM

Pivot Tables are designed to do just what you're looking for.

<Data><Pivot Table>
Use: Excel
Select your data, including headings from the Data sheet
Click the [Layout] button

PAGE: Drag the Location field here
ROW: Drag the Type field here
COLUMN: Drag the DATE field here
DATA: Drag the Total field here
-If it doesn't list as Sum of Total...dbl-click it and set it to Sum
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list
-each Type down the left side
-each Date across the top
-and calculate the total for each intersection of those two.

The default Page setting is to display totals for ALL locations, but to see just one Location, click the dropdown and choose one.

To refresh the Pivot Table, just right click it and select Refresh Data

If the rows in the data range may vary, consider basing the Pivot Table on a Dynamic Range Name. For instructions, see Debra Dalgleish's website:
http://www.contextures.com/xlNames01.html#Dynamic

Using your data and that above method, here's a sample fo the values I received:

LOCATION	LONDON			
				
Sum of TOTAL		
	DATE			
TYPE	9-Aug-07		10-Aug-07		11-Aug-07		12-Aug-07
A	31		34		18		20
B	21		31		23		25
C	63		73		51		46
D	23		19		17		18
E	1		3				1

Is that something you can work with?