How to quickly count visible or filtered cells only in Excel?

Generally, in Excel, the normal formula =COUNT(B3:B16) will count the number of all invisible cells and hidden cells. But in this tutorial, I will introduce some quick ways to help you count the visible or filtered cells only in Excel.

Count visible cells only with User Defined Function

Count visible cells only with Kutools for Excel

Count visible cells only with User Defined Function

Use a User Defined Function to count the visible cells only.

1. Press Alt + F11 keys simultaneously, a Microsoft Visual Basic for Applications window pops out.

2. Click Insert > Module, and paste below VBA code to the popped out Module window.

VBA: Count visible cells only.

Function COUNTVISIBLE(Rg)
'UpdatebyKutoolsforExcel20151208
    Dim xCount As Long
    Dim xCell As Range
    Application.Volatile
    For Each xCell In Rg
        If (Not xCell.EntireRow.Hidden) And (Not xCell.EntireColumn.Hidden) Then
            xCount = xCount + 1
        End If
    Next
    COUNTVISIBLE = xCount
End Function

3. Then save the code and close the Module window, and then select a cell that output result, and type this formula =COUNTVISIBLE(B3:B16), and press the Enter key to get the result. See screenshots:

>

Count visible cells only with Kutools for Excel

To count visible cells only, you also can use Kutools for Excel’s COUNTVISIBLE function to quickly solve it.

After free installing Kutools for Excel, please do as below:

1. Select a cell which will put the counting result in, and click Kutools > Kutools Functions > Statistical & Math > COUNTVISIBLE. See screenshot:

2. Then in the Function Arguments dialog, select the filtered cells you want to count into the Reference textbox, and you can preview the counting result has been shown in the dialog. See screenshot:

3. And then click OK to put the counting result in the selected cell.

Kutools for Excel's Functions including some useful formulas, such as count/sum/average visible cells only, count/sum by cell color. count/sum by font color and so on.To Get Free Trial Now!

Relative Articles:

• How to average visible or filtered cells only in Excel?
• How to average values based on cell color in Excel?
• How to count if cells are start with or end with specific text in Excel?
• How to count if cells are start with or end with specific text in Excel?