Use the SUBTOTAL function to prevent double counting

Keyboard Shortcuts

Use the SUBTOTAL function to tabulate data so that if the cell if later referenced, its totals will not be tabulated twice. Totaling a column of data is difficult when the column contains detailed data and SUM functions. Use the SUBTOTAL function instead of SUM when using multiple levels of totals.

- [Voiceover] On this worksheet called Subtotal…we've got a list of data over in columns A through E,…not terribly long.…This list was actually copied from a list…we see farther to the right.…And I manually manipulate it,…put in some empty rows for putting in subtotals.…Now, Excel has a subtotal capability on the data tab…that probably I would have used,…but maybe I got this data from someone else,…so I am manipulating it in a different way.…The list to the right contains the original entries.…If I click column O here,…in this status bar I see the total…is two and a half million exactly.…

Now, off to the left,…I've inserted those rows and put in totals…for each group here,…for example there using the sum function…and here and so on down the list.…So as I click column E here,…we see the total in the status bar is five million,…exactly twice.…So we're getting what is called a double count.…As we work with this list over on the left-hand side,…we want to be mindful of the fact…that we are gonna be getting double totals…

Resume Transcript Auto-Scroll

Author

Released

5/26/2016

Conquer some of the most daunting features in Microsoft Excel: formulas and functions. In this Excel for Mac 2016 update to his popular series, author Dennis Taylor presents numerous formulas and functions in Excel and shows how to use them efficiently. The course begins with tips and keyboard shortcuts to accelerate the way you work with formulas within one or multiple worksheets. Dennis then covers how to perform logical tests with the IF, AND, OR, and NOT functions; search and retrieve data with lookup functions (VLOOKUP, MATCH, and INDEX); analyze data with statistical functions; use text functions to clean up worksheets; work with array formulas and functions, and master date and time calculations. Dennis focuses on practical examples that transition effortlessly to real-world scenarios. Start watching to take advantage of the tremendous power offered by Excel formulas and functions.

Topics include:

Understanding how the hierarchy of operators affects formula results

Knowing when to use absolute vs. relative references

Using Formula Builder for unfamiliar functions

Displaying a worksheet's formulas and highlighting formula cells

Converting formulas to values

Creating 3D formulas to gather data from multiple sheets

Creating and expanding nested IF functions; using AND, OR, and NOT with IF

Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX

Analyzing data with the statistical functions: MEDIAN, MODE, etc.

Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more