In Excel we often like to Count Things. Sometimes those things are Cells with Text, Formulas or Formatting. Other times we want to Count Blank or Non-Blank Cells… and so on. Today I will teach you All About Counting Things in Excel. Excel Count Functions are an obvious option to go with, but there are also wonders you can do with just 1 line of VBA Code.

If want to see a Cool Summary of all your Excel Workbook Statistics scroll down to the last section.

Excel Count Functions

First let us start with Basic Excel Count Functions:

Excel COUNT function

Counts Number of Cells with Numbers

=COUNT( value1, [value2], ...)

Excel COUNTA> function

Counts Number of Non-Blank Cells

=COUNTA( value1, [value2], ...)

Excel COUNTBLANK function

Counts Number of Blank Cells

=COUNTBLANK( value1, [value2], ...)

Excel COUNTIF function

Counts Number of Cells that Fulfill an If Condition

=COUNTIF( range, criteria )

Excel DCOUNT function

Counts Number of Cells that Fulfill MULTIPLE Specified Conditions

=DCOUNT( database, field, criteria )

VBA Count Functions

VBA Count Cells in Range

To simply count the number of VBA Cells in an Excel Range use the Count Range property.

Debug.Print Range("A1:A5").Count

VBA Count Cells with Numbers

To Count Cells with Numbers (equivalent to Excel COUNT Function) use the WorksheetFunctions.Count function:

Debug.Print WorksheetFunction.Count(Range("A1:A5"))

VBA Count Non-Blank Cells

To Count Non-Blank Cells (equivalent to Excel COUNTA Function) use the WorksheetFunctions.CountA function:

Debug.Print WorksheetFunction.CountA(Range("A1:A5"))

VBA Count Blank Cells

To Count Blank Cells (equivalent to Excel COUNTBLANK Function) use the WorksheetFunctions.CountBlank function or the SpecialCells Range property:
CountA:

Debug.Print WorksheetFunction.CountA(Range("A1:A5"))

SpecialCells:

Debug.Print Range("A1:A5").SpecialCells(xlCellTypeBlanks).Count

VBA Count If Cells

To Count If Cells (equivalent to Excel COUNTIF Function) on certain conditions use the WorksheetFunctions.CountIf function:

Debug.Print WorksheetFunction.CountIf(Range("A1:A5"), ">1")

VBA Count Cells with Formulas

To Count Cells with Formulas use the SpecialCells Range property:

Debug.Print Range("A1:A5").SpecialCells(xlCellTypeFormulas).Count

VBA Count Cells with Constants (Non-Formulas & Non-Blank)

To Count Cells with Constants use the SpecialCells Range property:

Debug.Print Range("A1:A5").SpecialCells(xlCellTypeConstants).Count

VBA Count Visible / Invisible Cells

To Count Cells with Constants use the SpecialCells Range property:
Count Visible Cells:

Debug.Print Range("A1:A5").SpecialCells(xlCellTypeVisible).Count

Count Invisible Cells:

Debug.Print Range("A1:A5").Count - Range("A1:A5").SpecialCells(xlCellTypeVisible).Count

VBA Count Other Types of Cells

The SpecialCells Range property allows you to find a subset of certain types of cells. Here are all the available types:

Excel Function Usage Statistics

I saved best for last. What if you wanted to do a statistic on the Excel Functions used in your Excel Workbook? Excel doesn’t provide you with a neat statistics windows like Word for words and sentences. Fortunately, I developed a neat VBA Macro that Counts every Function used in all Excel Formulas and presents a neat report as a result.

The Code

The Code below will generate Excel Function Usage Statistics to a new Worksheet.

Option Explicit
Sub AppendDict(dict As Object, func As Variant)
    Dim c As Long
    On Error Resume Next
    c = dict(func)
    dict.Remove func
    dict.Add func, c + 1
End Sub
Function RegexExecute(str As String, reg As String, Optional findOnlyFirstMatch As Boolean = False) As Object
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = reg
    regex.Global = Not (findOnlyFirstMatch)
    If regex.Test(str) Then
        Set RegexExecute = regex.Execute(str)
        Exit Function
    End If
End Function

Function GetAllFunctions(formula As String) As Collection
    Dim funcCol As Collection, matches As Object, match As Object
    Set funcCol = New Collection
    Set matches = RegexExecute(formula, "([A-Za-z]*?)\(")
    For Each match In matches
        funcCol.Add match.Submatches(0)
    Next match
    Set GetAllFunctions = funcCol
End Function

Sub CreateStats()
    Dim ws As Worksheet, rng As Range
    Dim constantCount As Long, functionDict As Object, funcCol As Collection, func As Variant
    Set functionDict = CreateObject("Scripting.Dictionary")
    For Each ws In ThisWorkbook.Worksheets
        For Each rng In ws.Cells.SpecialCells(xlCellTypeFormulas)
            Set funcCol = GetAllFunctions(rng.formula)
            For Each func In funcCol
                AppendDict functionDict, func
            Next func
        Next rng
        constantCount = constantCount + ws.Cells.SpecialCells(xlCellTypeConstants).Count
    Next ws
    ExportStats ThisWorkbook.Worksheets.Count, constantCount, functionDict
    Set functionDict = Nothing
    Set funcCol = Nothing
End Sub

Sub ExportStats(worksheetsCount As Long, constantsCount As Long, funcDict As Object)
    Dim ws As Worksheet, formulaCount As Long, it As Variant, rowOffset As Long
    Dim maxVal, maxKey, key As Variant
    Set ws = ThisWorkbook.Worksheets.Add
    ws.Name = "Excel Stats"
    ws.Cells(1, 1) = "Worksheets:": ws.Cells(1, 2) = worksheetsCount
    ws.Cells(2, 1) = "Constants:": ws.Cells(2, 2) = constantsCount
    For Each it In funcDict.Items
        formulaCount = formulaCount + it
    Next it
    ws.Cells(3, 1) = "Functions:": ws.Cells(3, 2) = formulaCount
    ws.Range(ws.Cells(5, 1), ws.Cells(5, 2)).Merge
    ws.Cells(5, 1) = "Function Stats": ws.Cells(5, 1).Font.Bold = True
    Dim x As Range
    
    Do Until funcDict.Count = 0
        maxKey = funcDict.Keys()(0)
        maxVal = funcDict(maxKey)
        For Each key In funcDict.Keys
            If funcDict(key) > funcDict(maxKey) Then
                maxKey = key
                maxVal = funcDict(maxKey)
            End If
        Next key
        If maxKey <> vbNullString Then
             ws.Cells(6 + rowOffset, 2) = funcDict(maxKey): ws.Cells(6 + rowOffset, 1) = maxKey
        End If
        funcDict.Remove (maxKey)
        rowOffset = rowOffset + 1
    Loop
    ws.Columns("A:B").EntireColumn.AutoFit
End Sub

How to use it? Simply run the CreateStats Sub procedure:

CreateStats

Or go to the DEVELOPER Ribbon Tab, select Macros and Run the CreateStats procedure on the list.

An Example

Say we have an example Excel Workbook with various Formulas. The CreateStats procedure will produce the following statistics:

An Excel UserForm

Want a neat UserForm report like the one below?

Instead of using the CreateStats function you can alternatively use my CreateStatsUserForm function (using the StatsForm – see Download section below), to instead admire a neat statistics UserForm without having to manage any additional ad-hoc Worksheets.

Sub CreateStatsUserForm()
    Dim ws As Worksheet, rng As Range
    Dim constantCount As Long, functionDict As Object, funcCol As Collection, func As Variant
    Set functionDict = CreateObject("Scripting.Dictionary")
    For Each ws In ThisWorkbook.Worksheets
        For Each rng In ws.Cells.SpecialCells(xlCellTypeFormulas)
            Set funcCol = GetAllFunctions(rng.formula)
            For Each func In funcCol
                AppendDict functionDict, func
            Next func
        Next rng
        constantCount = constantCount + ws.Cells.SpecialCells(xlCellTypeConstants).Count
    Next ws
    Dim sf As StatsForm
    Set sf = New StatsForm
    sf.Populate ThisWorkbook.Worksheets.Count, constantCount, functionDict
    sf.Show
    Set functionDict = Nothing
    Set funcCol = Nothing
End Sub

Download Excel Stats

You can download the Excel Functions Usage Statistics Modules below:
Download