Menu
Insights Training
  • Home
  • Chat with Us
  • Webinars
  • Public Schedules
  • Course List
  • Clients / Class Pictures
    • 2017 Class Pics / Clients
    • 2018 Class Pics / Clients
    • 2019 Class Pics/ Clients
    • 2020 Class Pictures
    • 2021 Class Pics
    • 2023 Class Pics
    • 2024 Class Pics
  • Testimonials
  • Convert Courses to Online
  • Request for Proposal
  • Insights Online
  • Take an Assessment
  • Blog
  • Contact Us
  • OTHER SERVICES
  • Privacy Policy
  • Code Verifier
  • Blog
  • Refer and Earn
  • Admin Tools
  • Home
  • Chat with Us
  • Webinars
  • Public Schedules
  • Course List
  • Clients / Class Pictures
    • 2017 Class Pics / Clients
    • 2018 Class Pics / Clients
    • 2019 Class Pics/ Clients
    • 2020 Class Pictures
    • 2021 Class Pics
    • 2023 Class Pics
    • 2024 Class Pics
  • Testimonials
  • Convert Courses to Online
  • Request for Proposal
  • Insights Online
  • Take an Assessment
  • Blog
  • Contact Us
  • OTHER SERVICES
  • Privacy Policy
  • Code Verifier
  • Blog
  • Refer and Earn
  • Admin Tools

Blog

Remove Hidden Rows in Calculation using AGGREGATE

9/22/2017

Comments

 
New in Excel 2010 is the AGGREGATE Function.  Have you ever had the need to redo a calculation because the total you had sums an entire column whereas suddenly, you realize you have to hide some rows?
 
In the screenshot below, you can immediately see that the items should not total at 14000.  Upon further checking, you may see that some rows were hidden, and the formula =SUM(E2:E15) adds all cells in the range, including those hidden rows.
Picture
With the AGGREGATE function, the cell with the total will update based on what is only visible in the worksheet.  The function uses codes to be created.  When you enter the open parenthesis after the function, Excel will show a dropdown list where you could choose the code number for your desired operation.  Since we want to add numbers, we choose 9.  =AGGREGATE(9, ... 
Picture
The next argument the function needs is what to ignore.  Here you can see that AGGREGATE also allows you to ignore error cells.  This is a way to resolve doing an AVERAGE but one of the cells in the range is an error (which means you will get an error too when you do an AVERAGE.)  Since we need to ignore hidden rows, we choose 5 in the option and type it in the formula.  =AGGREGATE(9,5 ...
Picture
Finally, add the range as the third argument.  =AGGREGATE(9,5,E2:E15)
 
The formula now updates based on what is only visible in the worksheet.
Picture
Comments
    View my profile on LinkedIn

    Archives

    April 2020
    May 2019
    September 2017

    Categories

    All
    Excel Functions
    Word Features

    RSS Feed

Home

Courses

Public Classes

Contact

Copyright © 2022