The AGGREGATE function gives you tabulation options, including SUM, AVERAGE, and 17 other functions, automatically displayed as you type the function. Other options let you ignore cells in a range that contain errors, are part of a hidden row, or those that contain AGGREGATE and SUBTOTAL functions.
- [Voiceover] In this worksheet called Aggregate,…I've got some data in columns A through D,…I simply want to get a total of the data in column B.…Maybe I'll do this in cell E1.…I'll use the AutoSum button.…It's on the right side of the Home tab,…or on the left side of the Formulas tab.…Here I am on the Home tab, click AutoSum,…select column B and Return, I've got an answer.…I want to do this for columns C and D,…so I'll simply take this formula…and drag that fill handle to the right, and what happens?…I don't have a total here.…Trying to add up the data in column C here,…and column D over here is not working.…
As I start to scroll through the list,…eventually I might see, whoops, there's a value error there.…There could be others.…There might be others down the list here.…There's one in column C, there's #VALUE!,…and here's an #REF! error.…We've got some problems here.…I don't have time to get to those problems right now.…I'd like to really be able to add up the data here…and ignore those error cells.…The Sum function doesn't give us a way to do this,…
Author
Dennis Taylor
Released
5/26/2016- 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
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets
Skill Level Intermediate
Duration
Views
-
Introduction
-
Welcome50s
-
Exercise files25s
-
-
1. Formula and Function Tools
-
2. Formula and Function Tips and Shortcuts
-
3. IF and Related Functions
-
4. Lookup and Reference Functions
-
Use MATCH and INDEX together4m 40s
-
5. Statistical Functions
-
6. Power Functions
-
7. Selected Financial Functions
-
8. Math Functions
-
9. Date and Time Functions
-
10. Text Functions
-
11. Array Formulas and Functions
-
12. Reference Functions
-
13. Information Functions
-
Conclusion
-
Next steps25s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Bypass errors and hidden data with AGGREGATE