Exclude Cells that are Filtered, Hidden or Grouped from Formulas

Add to Favorites
Author: | Edits: don

2 different Powerful data analysis formulas for Excel that allow you to exclude hidden, grouped, or filtered data from calculations and that will also allow you to avoid issues caused by errors in the data set.

Normally, functions in Excel will include data that is hidden or filtered - this means that if you want to count only the visible rows of data after you have filtered them, the normal COUNT() function will not work - this causes an issue with data analysis in Excel where the values that you see, output from the formulas, do not correspond with what you actually see in the spreadsheet.

This tutorial will cover 4 distinct scenarios and show you how to perform analysis on only the visible results:

  • filtering data in Excel
  • grouping data in Excel
  • manually hiding data in Excel
  • data sets that include errors in them, regardless of whether the errors are hidden or not

I hope you enjoy this tutorial!

Downloadable Files: Excel File

Question? Ask it in our Excel Forum

Our Excel Courses

  • Skill Level: All Levels
  • |
  • 5 hours+

How to make a fully featured professional form in Excel that is unbreakable. This includes how to use the form to store, view, edit, and delete data from a data storage worksheet.

  • Skill Level: All Levels
  • |
  • 2 hours

Send Emails from Excel using VBA and Macros. This course starts from the Basics and builds up to more advanced examples with attaching workbooks, worksheets, PDF's, automatically sending emails, including a signature, error handling, increasing speed, and more.

Similar Content on TeachExcel
Count the Visible Rows in a Filtered List in Excel
Tutorial: How to use the COUNT or COUNTA function on a filtered list of data so that hidden rows ar...
Sum the Visible Rows from a Filtered List in Excel
Tutorial: How to SUM only the visible rows from a filtered data set in Excel. To do this, we will us...
Filter and Sort Data on Charts in Excel
Tutorial: Create a dynamic chart in Excel that displays only the data you want. You can filter it an...
Prevent Cells from Summing to a Negative Value or Vice Versa
Tutorial: How to prevent a range of cells from adding up to a negative value, or preventing the same...
Quickly See All Cells linked to a Formula or Function in Excel
Tutorial: Ill show you how to see the cells used in a formula/function in Excel and also how to tel...
Count the Number of Cells that Contain Specific Text in Excel
Tutorial: How to count the number of cells that contain specific text within a spreadsheet in Excel....