# Excel Count Cells with Text and Formula – Excel Stats

(1 votes, average: 5.00 out of 5)

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

### Excel COUNTA function

Counts Number of Non-Blank Cells

### Excel COUNTBLANK function

Counts Number of Blank Cells

### Excel COUNTIF function

Counts Number of Cells that Fulfill an If Condition

### Excel DCOUNT function

Counts Number of Cells that Fulfill MULTIPLE Specified Conditions

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

### VBA Count Cells with Numbers

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

### VBA Count Non-Blank Cells

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

### VBA Count Blank Cells

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

SpecialCells:

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:

### VBA Count Cells with Formulas

To Count Cells with Formulas use the SpecialCells Range property:

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:

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:

Count Invisible Cells:

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

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

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?

## 3 thoughts on “Excel Count Cells with Text and Formula – Excel Stats”

1. Good article. We’ve added it to our collection of the most useful and interesting spreadsheet-related articles from the web at http://www.i-nth.com/resources/connexion

For the VBA code to work, the user needs to save their workbook as an “Excel Macro-Enabled Workbook (*.xlsm)”.
The code also requires that each worksheet contains at least one formula and at least one non-formula (otherwise it will crash). It would be good to add a check for the situation where a worksheet contains no formulae and/or no constants.

Cheers,

Bob

http://www.i-nth.com

1. Thanks Bob, good call-outs there – will definitely need to look into adding a condition to check if there are no formulas on the Worksheet.

2. Thanks a lot for this awesome website
I encountered an error at this line

Object required .. Is there any reference that I have to add?