How to Sum only Visible Cells in Excel? How to Sum only Visible Cells in Excel? As there are different types of problem we face while using excel , as in last post i tried to explain how can we remove hidden rows, it was also for the sake of simplicity and to calculate only visible cells, However that method is good if you want to show only filtered data and you do not want to save irrelevant data.

Now if you want to sum only visible cells and do not want to include hidden rows in it this tutorial is for you. So today’s question is:

How to Sum only Visible Cells in excel?

or

Do not sum hidden values in excel

There is a simple method which we can use to calculate only visible cells:
=SUBTOTAL(109,E1:E2)
where E1:E2 will be range of cells you want to calculate,
Also it is a matter of fact that if data is filtered and cells are not manually hidden than simple formula of =SUBTOTAL(9,E1:E2) will also work and will only calculate only visible cells. However if Cells are manually hided than we need to use =SUBTOTAL(109,E1:E2) to calculate to sum all cells except hidden ones.
This can be seen clearly in below image that when we hide 3rd value manually =SUBTOTAL(109,E1:E2) works for manually hided cells while =SUBTOTAL(9,E1:E2) did not work:

Do not sum hidden values in excel<

Do not sum hidden values in excel.

Categorized in:

Excel,

Last Update: November 9, 2017