Excel – Add Cells While Ignoring Hidden Cells

This is a way to add up the contents of cells, while ignoring hidden cells, eg, if you’ve filtered a list, and need to add up the contents of a column of visible cells. (Because just using SUM would not work because it would also include the hidden cells.)

 So, to do this…

Say that you want to add the numbers in cells A1, A2, A3 and A4. You can do so with the formula =sum (A1:A4). However, you only want to use that formula if those cells are not hidden (to hide and unhide rows and columns, go to Home | Cells | Format | Hide & Unhide).

Instead, (in this example) use the formula =SUBTOTAL(109, A1:A4).

The SUBTOTAL function can add, subtract or average numbers, among other calculations. The first argument tells SUBTOTAL what kind of calculation to perform. 9 means sum. 109 means “sum, but ignore hidden cells”. The following arguments are the cells, ranges or numbers to sum. So this formula is like =sum (A1:A4), but any hidden cells in that range are ignored.

Thanks to Able Owl Excel tips for this one.


For great time-saving tips, look up our Archive of Tech Tips or look through the the Technology Information for Staff website.

Was this tip helpful to you? Anything else you want to know? Please leave a comment below.

You’ll find more learning at Learning and Development.