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: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
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
Introduction
Excel Versions 00:00:32
Sum Total on Status Bar 00:01:26
Autosum Feature 00:05:23
Smarter SUM Formulas 00:07:11
Drill Through Worksheets 00:12:14
INDIRECT Function 00:17:07
SUM/OFFSET Functions 00:27:08
SUBTOTAL Functions 00:33:05
Subtotal Feature 00:40:16
Formatting Subtotals 00:47:32
AGGREGATE Function 00:49:54
SUMIF Function 00:55:39
Introduction to SUMIF 01:01:25
SUMIF with Wildcard Criteria 01:07:14
SUMIF with Comparison Operators 01:11:27
SUMIF/OFFSET Functions 01:15:01
Data Validation - LIst of Months 01:20:33
Introduction to SUMIFS 01:24:55
SUMIFS Function with One Range Criteria 01:29:46
SUMIFS Function with Two Range Criteria 01:31:08
Introductions to SUMPRODUCT 01:35:02
SUMPRODUCT as SUMIFS Alternatives 01:37:28
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
