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

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

### Excel COUNTA> function

Counts Number of **Non-Blank** Cells

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

### Excel COUNTBLANK function

Counts Number of **Blank** Cells

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

### Excel COUNTIF function

Counts Number of Cells that **Fulfill an If Condition**

1 |
=COUNTIF( range, criteria ) |

### Excel DCOUNT function

Counts Number of Cells that **Fulfill MULTIPLE Specified Conditions**

1 |
=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.

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

**VBA Range**read my

**VBA Range Tutorial**

### VBA Count Cells with Numbers

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

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

1 |
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:**

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

**SpecialCells:**

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

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

### VBA Count Cells with Formulas

To **Count Cells with Formulas** use the **SpecialCells** Range property:

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

1 |
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:**

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

**Count Invisible Cells:**

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

xlCellTypeComments | Cells with Comments |

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
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:

1 |
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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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: