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

total sum subtotal

After hiding row no.3

total without hidden row

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

More about SUBTOTAL



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress