How to exclude hidden rows from SUM

SUM function always calculates with all cells in the range. Sometimes, it’s not desired behavior and we want to exclude hidden cells.

Using SUBTOTAL to ignore hidden rows in SUM

SUBTOTAL is a very special function. It belongs to the Math & Trig functions. SUBTOTAL can do many operations like SUM, AVG, MIN, MAX, … and it can ignore hidden rows. How it works? The first argument is number of function. To sum and ignore hidden rows write the number 109. The second argument is range to sum.

No hidden rows

After hiding row no.3

This solution works for hidden rows only. For hidden columns must be used VBA function (Excel macro).

More about SUBTOTAL