Six Ways to Sum in Excel

In this presentation author and Excel expert David Ringstrom, CPA shares several worksheet functions with you that will enable you to sum data in a variety of ways. Techniques include maximizing the statistics that appear on Excel's Status Bar, using the AutoSum feature, and future-proofing the SUM function. David will also compare the SUBTOTAL function with the Subtotal feature, contrast SUMIF and SUMIFS, demonstrate SUMPRODUCT, and much more.

David is the author of “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and has written or co-authored six other books. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.

Who Should Attend
Professionals seeking to use Microsoft Excel more effectively.

Topics Covered

• Summing results from multiple columns with a single XLOOKUP function nested within a SUM function
• Using Excel’s OFFSET function to dynamically reference data from one or more accounting periods
• Improving the integrity of spreadsheets by using SUMIF to look up values in a more flexible fashion than VLOOKUP
• Extending the functionality of the SUMIF function by incorporating wildcard characters within a formula
• Verifying sums and totals quickly by simply selecting cells with your mouse
• Using the SUMIFS function to sum values based on multiple criteria
• Learning a simple design technique that greatly improves the integrity of Excel’s SUM function
• Incorporating comparison operators within SUMIF to sum numbers based on range criteria, such as greater than, less than, and so on
• Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets
• Removing duplicates from a list with the new UNIQUE function
• Exploring how the INDIRECT worksheet function can automate pulling values from a group of individual worksheets
• Applying color to a range of cells dynamically with Conditional Formatting based upon an input cell that specifies the number of cells to format

Learning Objectives

• Demonstrate how the AGGREGATE function provides error-handling capabilities that make it superior to SUM and SUBTOTAL in cases where errors exist in the dataset
• Identify the purpose of the OFFSET function
• Understand how XLOOKUP retrieves an array of values from a table and how SUM can be used to total the results

Level
Intermediate

Instructional Method
Group: Internet-based

NASBA Field of Study
Specialized Knowledge and Applications (2 hours)

Program Prerequisites
Prior experience with Microsoft Excel is recommended.

Advance Preparation
None