Count cells that are not hidden

The functions COUNT, COUNTA and COUNTIFS all include hidden cells.
It is possible to only count cells in a range that are currently visible.
SS - B2:D10 populated
SS - count = 24, counta = 24, countifs(>0) = 24


aggregate(2,5,B2:D10 = 12
subtotal(102,B2:D10) = 12


Column "C" has been manually hidden
Rows "5" and "6" have been manually hidden



Status Bar Information

The status bar at the bottom can also provide a count but be careful.
When you have hidden rows the status bar will exclude them.
But when you have hidden columns the status bar does not exclude them.
To make sure the status bar is always correct you need to only select the visible cells in a range.
Select the whole range ("B2:D10") and press (Home > Find & Select > Go) to display the GoTo dialog box.
Press Special and select "visible cells only". Press OK.


Built-in Functions

COUNT - The number of numerical values.
COUNTA - The number of numerical values (including logical values and text).
COUNTIFS - The number of numerical values that satisfies multiple conditions.
AGGREGATE - The choice of 19 different operations for numerical values which can ignore hidden rows and errors.
SUBTOTAL - (AGGREGATE) The choice of 11 different operations for numerical values (renamed).


User Defined Function

COUNTVISIBLE - Returns the
COUNTVISIBLEIFS - Returns the


Related Formulas

Count cells that are hidden
Sum cells that are not hidden
Average cells that are not hidden
Sum visible rows with 1 condition
Sum visible rows with multiple conditions


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top