Skip to main content

Kutools for Office ā€” One Suite. Five Tools. Get More Done.

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

Author Sun Last modified

Generally, in Excel, the formula =COUNT(B3:B16) will count both visible 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.

A screenshot of data with hidden rows in Excel

Count visible cells only with User Defined Function

Count visible cells only with Kutools for Excel good idea3

arrow blue right bubble 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 to display the output result, and type this formula =COUNTVISIBLE(B3:B16), and press the Enter key to get the result. See screenshots:

A screenshot of the COUNTVISIBLE formula applied to a data range in Excel
Arrow
A screenshot of the COUNTVISIBLE formula result showing visible cell count in Excel

arrow blue right bubble 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.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

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

A screenshot showing Kutools for Excel's COUNTVISIBLE function under the Statistical & Math menu

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:

A screenshot of Kutools Function Arguments dialog for counting visible cells in Excel

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

A screenshot showing the COUNTVISIBLE result in Excel

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. Get A Free Trial Now!

A screenshot of additional Kutools for Excel functions for visible cell counting and cell color-based calculations

Related Articles:

Best Office Productivity Tools

šŸ¤– Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functionsā€¦
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language ā€“ supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite ā€” Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license ā€” set up in minutes (MSI-ready)
  • Works better together ā€” streamlined productivity across Office apps
  • 30-day full-featured trial ā€” no registration, no credit card
  • Best value ā€” save vs buying individual add-in