On Demand Webinar

Excel Agility: Six Ways to Sum

Webinar Details $219

  • Rated:
  • Webinar Length: 100 Minutes
  • Guest Speaker:   David Ringstrom
  • Topic:   Business Administration, Business Skills, Finance, Taxation and Accounting, Software
  • Credit:   CPE 2.0
All Access Membership

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


  1. Introduction

  2. Excel Versions 00:00:32

  3. Sum Total on Status Bar 00:01:26

  4. Autosum Feature 00:05:23

  5. Smarter SUM Formulas 00:07:11

  6. Drill Through Worksheets 00:12:14

  7. INDIRECT  Function 00:17:07

  8. SUM/OFFSET Functions 00:27:08

  9. SUBTOTAL Functions 00:33:05

  10. Subtotal Feature 00:40:16

  11. Formatting Subtotals 00:47:32

  12. AGGREGATE Function 00:49:54

  13. SUMIF Function 00:55:39

  14. Introduction to SUMIF 01:01:25

  15. SUMIF with Wildcard Criteria 01:07:14

  16. SUMIF with Comparison Operators 01:11:27

  17. SUMIF/OFFSET Functions 01:15:01

  18. Data Validation - LIst of Months 01:20:33

  19. Introduction to SUMIFS 01:24:55

  20. SUMIFS Function with One Range Criteria 01:29:46

  21. SUMIFS Function with Two Range Criteria 01:31:08

  22. Introductions to SUMPRODUCT 01:35:02

  23. SUMPRODUCT as SUMIFS Alternatives 01:37:28

  24. Presentation Closing 01:439:31

  • AGGREGATE Function 00:49:58
  • AUTOSUM 00:05:28
  • Concatenation 00:19:12
  • Data Validation 01:20:52
  • INDIRECT Function 00:17:28, 00:21:17
  • OFFSET Function 00:27:23, 01:15:28
  • Subtotal Feature 00:40:19
  • SUBTOTAL Function 00:33:16
  • SUM 00:01:26, 00:07:35, 00:12:54, 00:50:33
  • SUMIF 00:55:51, 01:01:27, 01:15:06
  • SUMIFS 01:25:24
  • SUMPRODUCT 01:35:08
  • VLOOKUP 00:56:04
  • Wildcards 01:07:33


Guest Speaker

  • David H. Ringstrom, CPA

CPE Credit

Continuing Professional Education

Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or training@auroratrainingadvantage.com.

You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.