I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

Sumproduct _Comparison

We show a comparison of three different approaches to form a statistic on about 30,000 records of data:

We have a data table with 30,000 rows of data: Each row consists of a department key, a date, cost and identifier. Now we want to show for April 2010 the accumulated costs per department (leftmost column) and per identifier (topmost row).

sbSumproductComparison_01_Screen

If you are interested in downloading a 1.7 MB Excel 2010 © sample file which contains the function shown here, go to my Download page, please.

1. Worksheet formulas

Sheet "Worksheet Formula" is built with SUMPRODUCT worksheet formulas. You need to specify all different departments and Id's correctly and completely or the statistic will not be correct. The MONTH-part of the formula is dangerous because data might be older than 1 year. This sheet needs about 1,200ms (1.2s) to be calculated (please see sheet "FastExcel" for details).

sbSumproductComparison_02_Screen

Sheet "Worksheet Formula (2)" is also built with SUMPRODUCT worksheet formulas. It fixes the MONTH issue but its runtime is about 10,600ms (10.6s).

sbSumproductComparison_03_Screen

Sheet "Worksheet Formula (3)" is built with SUMIFS worksheet formulas. It needs a helper column YearMonth in sheet tblProcessorActivity and runs about 270ms + 110ms = 0.38s.

sbSumproductComparison_07_Screen

2. VBA

Sheet "VBA" is built with a VBA macro. All different departments and Id's will be collected automatically. This sheet needs about 860ms (0.86s) to be calculated (according to FastExcel).

sbSumproductComparison_01_Code
sbSumproductComparison_02_Code

3. Pivot table

Sheet "Pivot" is showing a pivot table. All different departments and Id's will be collected automatically. But you need to filter the date range manually. It is being refreshed by a VBA macro. This sheet needs about 170ms (0.17s) to be refreshed (according to FastExcel) plus your manual time to filter the date range.

sbSumproductComparison_03_Code

Runtimes have been calculated on my dual core laptop.

Sulprobil   Get it done   Contact   Disclaimer   Download