Tools of the Trade: Summing Functions
Neale Blackwood · 2014
Excel's main reporting functions are all summing functions. These are all explained and demonstrated. Excel's fixed, mixed, and relative references enable you to create more flexible formulas. Helper cells are introduced as a method to simplify formula creation. The SUM function has 3D capabilities, which means that it can sum through spreadsheets. This provides an easy way to summarize spreadsheets that have identical layouts. The SUBTOTAL function should be used for all your subtotaling needs because it correctly handles subtotals. Excel has a new function called AGGREGATE, which works similar to SUBTOTAL but has the added ability to ignore errors. The SUMIF and SUMIFS functions provide conditional summing capabilities. The SUMPRODUCT function is probably Excel's most flexible function; it can provide conditional summing and counting as well as other conditional calculations. The SUMPRODUCT function can work in similar ways to array formulas.