Range Quick Stats: Instant Sum, Count, and Cell-Type Breakdown for Any Selection

TL;DR: Select any range with your mouse and this macro instantly reports everything you’d normally write four or five formulas to find out — total cells, numeric count with sum and average, blanks, text entries, formulas, and error cells. Works on any selection in any workbook. One click, zero formulas to delete.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook with any kind of data — numbers, text, formulas, or errors
• The range you want to analyze must be on the active sheet (the macro works on whatever sheet you’re currently viewing)

Limitations:

• Reports stats on the selected range only — does not scan all sheets
• Numbers display in dollar format by default — change the Format() string in the code for other currencies or plain numbers
• Hidden rows and filtered-out cells are included in the count and sum — this macro analyzes the range as-is, not visible cells only
• Very large selections (tens of thousands of cells) may pause briefly while SpecialCells runs
• The message box can only display a limited amount of text — if you need a permanent record, adapt the macro to write to a sheet (see Adapt It)

#The Macro

Option Explicit

Sub QuickRangeStats()
    ' ── Range Quick Stats ─────────────────────────────
    ' Prompts for a range selection with the mouse, then
    ' reports total cells, numeric count/sum/average,
    ' blanks, text, formulas, and errors in a MsgBox.
    ' Non-destructive — reads cells, never writes.
    ' ───────────────────────────────────────────────────

    Dim rng As Range
    Dim totalCells As Long, numericCells As Long
    Dim blankCells As Long, textCells As Long
    Dim formulaCells As Long, errorCells As Long
    Dim numSum As Double, numAvg As Double
    Dim msg As String

    ' ── State management ──────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Error handling ────────────────────────────────
    On Error GoTo CleanUp

    ' ── Get the range ─────────────────────────────────
    On Error Resume Next
    Set rng = Application.InputBox( _
        "Select a range to analyze:", _
        "Range Quick Stats", Type:=8)
    On Error GoTo CleanUp

    If rng Is Nothing Then GoTo CleanUp

    ' ── Count numeric cells ───────────────────────────
    numericCells = WorksheetFunction.Count(rng)

    ' ── Count by cell type ────────────────────────────
    On Error Resume Next
    blankCells = rng.SpecialCells(xlCellTypeBlanks).Count
    If Err.Number <> 0 Then blankCells = 0
    Err.Clear

    textCells = rng.SpecialCells( _
        xlCellTypeConstants, xlTextValues).Count
    If Err.Number <> 0 Then textCells = 0
    Err.Clear

    formulaCells = rng.SpecialCells( _
        xlCellTypeFormulas).Count
    If Err.Number <> 0 Then formulaCells = 0
    Err.Clear

    errorCells = rng.SpecialCells( _
        xlCellTypeFormulas, xlErrors).Count
    If Err.Number <> 0 Then errorCells = 0
    On Error GoTo CleanUp

    ' ── Compute sum and average ───────────────────────
    totalCells = rng.Cells.Count

    If numericCells > 0 Then
        numSum = WorksheetFunction.Sum(rng)
        numAvg = WorksheetFunction.Average(rng)
    End If

    ' ── Build the message ─────────────────────────────
    msg = "Range: " & rng.Address(False, False) & _
          vbCrLf & vbCrLf & _
          "Total Cells: " & Format(totalCells, "#,##0") & _
          vbCrLf & _
          "Numeric: " & Format(numericCells, "#,##0") & _
          vbCrLf & _
          "  Sum: " & Format(numSum, "$#,##0.00") & _
          vbCrLf & _
          "  Avg: " & Format(numAvg, "$#,##0.00") & _
          vbCrLf & _
          "Blanks: " & Format(blankCells, "#,##0") & _
          vbCrLf & _
          "Text: " & Format(textCells, "#,##0") & _
          vbCrLf & _
          "Formulas: " & Format(formulaCells, "#,##0") & _
          vbCrLf & _
          "Errors: " & Format(errorCells, "#,##0")

    MsgBox msg, vbInformation, "Range Quick Stats"

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & _
               Err.Description, vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#Mouse selection, not typing

Application.InputBox(Type:=8) opens a range-picker dialog. You select cells with your mouse instead of typing a range address. It’s the same interface Excel uses when you click the range selector button next to any formula bar. Cancel returns Nothing, so the macro exits cleanly without a runtime error.

Set rng = Application.InputBox( _
    "Select a range to analyze:", _
    "Range Quick Stats", Type:=8)
If rng Is Nothing Then GoTo CleanUp

#WorksheetFunction.Count catches every numeric cell

Count() counts all cells containing numbers — including formulas that evaluate to numbers. It skips text, blanks, booleans, and errors. This is the same behavior you get from =COUNT() in a worksheet formula. Using it means you get one reliable number for “how many cells feed into a total” without a manual loop.

numericCells = WorksheetFunction.Count(rng)

#SpecialCells with a safety net

SpecialCells is powerful but temperamental: if no cells match the requested type, Excel raises an error instead of returning an empty collection. The pattern of wrapping each call in On Error Resume Next and checking Err.Number handles this gracefully. If a sheet has zero blank cells, blankCells stays at 0 instead of crashing.

On Error Resume Next
blankCells = rng.SpecialCells(xlCellTypeBlanks).Count
If Err.Number <> 0 Then blankCells = 0
Err.Clear

Each Err.Clear resets the error state before the next SpecialCells call, so a “no blanks” result doesn’t cascade into a false positive on “no text.”

#Format() makes numbers readable

Raw numbers in a MsgBox are ugly — 1847300.25 means nothing at a glance. Format(1847300.25, "$#,##0.00") displays $1,847,300.25. The #,##0 specifier adds thousands separators and the $ prefix signals dollar amounts without requiring the user to parse a raw double.

"  Sum: " & Format(numSum, "$#,##0.00")

#Why a MsgBox instead of a report sheet

Report sheets are great when you need to sort, filter, or save the data. But for a quick spot-check — hey, how many rows is this schedule? — a MsgBox is faster. It opens instantly, you read it, you close it. No sheet to find, no sheet to delete, nothing to explain to the next preparer who opens the workbook. The Adapt It section below shows how to switch to a report sheet if that’s what you need.

#Adapt It