Totals and Conditional Sums |
Importance of Speed | Faster Calculations | Microsoft's Advice | Bottlenecks/Size |
Lookups | Dynamic Ranges | Totalling | Generating Workbooks |
Multi-Level Calculations | Array Formulae | SUMIF Example | Worksheet Links |
Totals and Conditional SumsTotals as Final ResultsIf you need to produce totals and subtotals as part of the final results of your workbook try using Pivot Tables. Totals as Intermediate Results
SUM and SUMIF:Excels SUM and SUMIF
functions are frequently used over a large number of cells. Period-to-date and cumulative SUMsThere are two methods of doing period-to-date or cumulative SUMs: Suppose the numbers you want to cumulatively SUM are in column A and you want Column B to have the cumulative sum. You can create a formula in column B like =SUM($A$1:$A2) and drag it down as far as you need. The beginning cell of the SUM is anchored in A1, but because the finishing cell has a relative row reference it will automatically increase for each row. Or you can create a formula like =$A1 in cell B1 and like =$B1+$A2 in B2 and drag it down as far as you need. This calculates the cumulative cell by adding this rows number to the previous cumulative SUM. For 1000 rows the first method makes Excel do about 500000 calculations, but the second method only makes Excel do about 2000 calculations (250 times more efficient!). Subset AddressingWhen you have multiple sorted indexes to a table (for instance City within Area) you can often save significant calculation time by dynamically calculating the address of a subset range of rows (or columns) to use in the SUM or SUMIF. See SUMIF Example or the FastExcel Sample problem for examples of this technique. Wildcard SUMIF & COUNTIFYou can use the wildcard characters ? (any single character) and * (no character or any number of characters) as part of the SUMIF & COUNTIF criteria on alphabetic ranges. DSUMExcel's DSUM function is very fast and uses a criteria range external to the function to specify multiple conditions for the sum. These criteria ranges allow great flexibility, but are difficult to use and maintain for large numbers of DSUM functions. SubtotalsEmbedded Subtotals:You can create embedded Subtotals using the Data -> Subtotals command on sorted data. I do not recommend using embedded subtotals because of the danger of inadvertent double counting. The SUBTOTAL function.
Combined Subtotals and Totals.If you are working with multidimensional data tables you often need to reference both subtotal and total intermediate results. Make sure you calculate the totals as a SUM of the subtotals rather than as a SUM of the whole table. |
||||||||
|
||||||||