# Excel Count Cells with Text and Formula – Excel Stats

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

### 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
```
If SpecialCells returns no cells then the Count property will throw an error

### 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`
If SpecialCells returns no cells then the Count property will throw an error

### 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`
If SpecialCells returns no cells then the Count property will throw an error

### 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:

Constant Description
xlCellTypeAllFormatConditions Any type of Cells
xlCellTypeAllValidation Cells with Validation Criteria
xlCellTypeBlanks Blank Cells
xlCellTypeConstants Cells with Constants (Non-Formula & Non-Blank)
xlCellTypeFormulas Cells with Formulas (beginning with a =)
xlCellTypeLastCell Get Last Cell in the UsedRange
xlCellTypeSameFormatConditions Cells with Common Format Conditions
xlCellTypeSameValidation Cells with Common Validation Criteria
xlCellTypeVisible All Visible Cells

## 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
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
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
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) &gt; funcDict(maxKey) Then
maxKey = key
maxVal = funcDict(maxKey)
End If
Next key
If maxKey &lt;&gt; 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.

See also  Excel to Image - VBA Save Range or Workbook as Image

### 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?

```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
```