Excel Agility: Six Ways to Sum

Follow along as Excel expert David Ringstrom, CPA, explores several ways to sum data beyond the venerable SUM function. Expanding your knowledge of worksheet functions can markedly improve the resilience and integrity of your spreadsheets. Take summing to new levels with the SUBTOTAL and AGGREGATE functions, while you learn how to have SUMIF and SUMIFS add up values based upon up to 127 criteria. David also discusses the SUMPRODUCT function.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He’ll draw to your attention any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples used in the course.

Who Would Be Interested in This Course:

Practitioners seeking to understand Excel functions that can be used to sum data. 

Your Benefits of Attending:

• Discovering the capabilities of the SUMPRODUCT function.
• Comparing the AGGREGATE function in Excel 2010 and later to the SUBTOTAL function available in all versions of Excel.
• Inserting totals into lists with a few mouse clicks by way of Excel’s SUBTOTAL function.
• Verifying sums and totals quickly by simply selecting cells with your mouse.
• Learning a simple design technique that greatly improves the integrity of Excel’s SUM function.
• Using a wildcard character with SUMIF to summarize data based on a partial match.
• Summing disparate sections of a spreadsheet quickly with the SUBTOTAL function.
• Using the SUMIFS function to sum values based on multiple criteria.
• Using the SUMIF function to summarize data based on a single criterion.
• Using Excel’s OFFSET function to dynamically reference data from one or more accounting periods.

Learning Objectives:

• State which versions of Excel that the AGGREGATE function is available in.
• Name what the SUMIFS function returns if a match cannot be found.
• Recall the keyboard shortcut for the AutoSum feature.

Level:

Intermediate

Format:

On-Demand Webcast

Instructional Method:

Self-Study

NASBA Field of Study:

Computer Software & Applications (1.5 hours)

Program Prerequisites:

Some Experience with the SUM Function

Advance Preparation:

None