Summing, Counting, Or Averaging Visible Cells Only In Excel

Often when you use tools like filter, grouping or hiding rows in Excel you want to be able to do the summing, counting or averaging of the visible cells only. Alternatively perhaps you just want the total of cells even if there are errors in some of the cells. You want Excel to ignore the cells with errors.

Table of contents

• YouTube Visible cells only calculations in Excel
• Telling Excel to ignore the hidden or error cells
• AGGREGATE and visible cells only
• Summing Visible Cells only example
• Related

YouTube Visible cells only calculations in Excel

Telling Excel to ignore the hidden or error cells

As shown below, the totals show everything and if there is an error in the cells, the total is lost.

If you filter on the results (say all the 1st years only), the total you see is still adding up all the hidden cells.

There are a couple of ways to do this but in the newer versions of Excel there is a great function called AGGREGATE.

AGGREGATE and visible cells only

The aggregate function can be used to

• average,
• count (in 2 ways),
• find the maximum or minimum,
• determine the product,
• sum,
• obtain the median,
• extract the largest or smallest, and
• other statistical features

where you want Excel to:

• Ignore hidden rows and error values, or
• Ignore hidden rows (so visible cells only), or
• Ignore error values, or
• Ignore nested SUBTOTAL and AGGREGATE functions
• and several other options.

By combining these features you will be able to achieve summing, counting or averaging of visible cells only,

Summing Visible Cells only example

So in the example below, note that the total in G shows the total of all the rows above, and column F has an error message due to some errors in the cells (but note that there are some cells with numbers which we would want to add up)

Also, note that when we filter the rows to only 1st years, the totals still stay the same. You run the risk of assuming that the total is for those few cells.

In the cell below we can use the AGGREGATE function.

When you click in the cell and type ‘=AGGREGATE(‘ you will see the following dropdown option. You can choose the one you want to use, either by typing the number or clicking on it.

Once you have chosen and typed the comma, you then get to decide what Excel must do. There are several options which allow you to ignore errors, hidden cells and certain functions (SUBTOTAL, AGGREGATES) etc. Again you can either type the appropriate number or click on the one you want.

The last step is just to tell Excel where to look, in much the same way as with a normal SUM or AVERAGE.

Now notice when we filter on the 1st years, the one total gives us all the cells (visible and hidden) whereas the AGGREGATE total only adds the visible cells.

In column F the AGGREGATE function ignores the hidden cells AND the error cells and gives us an answer for what we see.

Related

Sum cells by colour with the Data Filter

SUMPRODUCT only visible cells

Select visible cells button

Excel spreadsheet open but not visible