Six Ways to Sum in Excel

Author: David H. Ringstrom

When you participate in this live presentation, you’ll learn from Excel expert David Ringstrom, CPA, about Excel’s six worksheet functions: SUM, SUBTOTAL, AGGREGATE, SUMIF, SUMIFS, and SUMPRODUCT. David also shares the pros, cons, limitations, and uses for each.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2021, 2019, 2016 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.

Microsoft 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 2021, Excel 2019, and so on.

Publication Date: March 2022

Designed For
Practitioners seeking to understand and apply Excel’s six summing functions.

Topics Covered

• Comparing the AGGREGATE function in Excel 2010 and later to the SUBTOTAL function available in all versions of Excel
• Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts
• Employing the SUMIF function to sum values related to multiple instances of criteria you specify
• Exploring the XLOOKUP worksheet function in Excel 2021 and Microsoft 365
• Exploring how the INDIRECT worksheet function can automate pulling values from a group of individual worksheets
• Extending the functionality of the SUMIF function by incorporating wildcard characters within a formula
• Improving the integrity of spreadsheets by using SUMIF to look up values in a more flexible fashion than VLOOKUP
• Incorporating comparison operators within SUMIF to sum numbers based on range criteria, such as greater than, less than, and so on
• Inserting totals into lists with a few mouse clicks by way of Excel's SUBTOTAL function
• Learning a simple design technique that greatly improves the integrity of Excel's SUM function
• Learning how to use SUMPRODUCT as an alternative to SUMIF or SUMIFS for retrieving numerical data from external workbooks
• Returning multiple columns of data with XLOOKUP from a single formula by using dynamic array functionality in Excel 2021 and Microsoft 365

Learning Objectives

• Recognize and apply the INDIRECT and OFFSET functions to create more flexible formulas
• Describe the capabilities of the SUMPRODUCT function
• Identify how to return multiple columns of data with XLOOKUP from a single formula by using dynamic array functionality in Excel 2021 and Microsoft 365
• Recognize the keyboard shortcut for the AutoSum feature
• Describe what the INDIRECT function does
• Identify how to return an exact match for the fewest arguments that you can provide for XLOOKUP
• Identify which version of Excel is XLOOKUP compatible
• Recognize which ribbon tab or menu contains the Subtotal feature
• Identify an appropriate use of an operator within SUMIF

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
Previous Experience Working in Excel.

Advance Preparation
None