Maximize Excel: Summing Data Six Ways​
 
Date: 10/21/2024
Time: 12:00 PM - 2:00 PM EST
CPE Credit: 2 hours

Excel expert David Ringstrom, CPA, covers six worksheet functions available for summing data in Excel: SUM, SUBTOTAL, AGGREGATE, SUMIF, SUMIFS, and SUMPRODUCT. You’ll learn the pros, cons, and limitations of each type of worksheet function, along with suggested uses for each. In addition, David shows you how to create more flexible formulas using the INDIRECT and OFFSET functions, and how to verify sums and totals quickly and easily.
 
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Office 365 version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.
 
Office 365 is a subscription-based product that provides new-feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.


Topics Covered

• Incorporating comparison operators within SUMIF to sum numbers based on range criteria, such as greater than, less than, and so on.
 
• Unearthing the key step in being able to format subtotaled cells without affecting hidden rows.
 
• Using the SUM function to drill through two or more worksheets.
 
• Learning a simple design technique that greatly improves the integrity of Excel’s SUM function.
 
• Using the SUMIFS function to sum values based on multiple criteria.
 
• Using the SUMIFS function to sum values based on multiple criteria.
 
• Inserting totals into lists with a few mouse clicks by way of Excel’s SUBTOTAL function.
 
• Exploring how the INDIRECT worksheet function can automate pulling values from a group of individual worksheets.
 
• Verifying sums and totals quickly by simply selecting cells with your mouse.
 
• Using the SUMIFS function to sum values based on multiple criteria.
 
• Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts.
 
• Summing disparate sections of a spreadsheet quickly with the SUBTOTAL function.

Learning Objectives:

• Apply the INDIRECT and OFFSET functions to create more flexible formulas.
• Define three different contexts for Excel’s SUMPRODUCT function.
• Identify how to use the SUBTOTAL function to streamline summing multiple areas of a spreadsheet.