Tag Archives: formulas

excel count formula

Excel Count Cells with Text and Formula – Excel Stats

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

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 Function

Excel COUNTA function

Counts Number of Non-Blank Cells

COUNTA Function

Excel COUNTBLANK function

Counts Number of Blank Cells

COUNTBLANK Function

Excel COUNTIF function

Counts Number of Cells that Fulfill an If Condition

COUNTIF Function

Excel DCOUNT function

Counts Number of Cells that Fulfill MULTIPLE Specified Conditions

DCOUNT Excel Function

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.

To learn more about the 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:

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

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

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

Excel Workbook Function Usage Statistics
Excel Workbook Function Usage Statistics

An Excel UserForm

Want a neat UserForm report like the one below?

Excel Workbook Function Usage Statistics
Excel Workbook Function Usage Statistics

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.

Download Excel Stats

You can download the Excel Functions Usage Statistics Modules below:

Excel Optimizer

Optimize Excel formulas with the Excel Optimizer!

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)
Loading...

Ever been in a situation where your Excel workbook runs so slow you could make yourself a coffee before it finishes recalculating? Fighting to reduce the file size of your latest Excel report? Or just looking to optimize your Excel workbook a bit? Welcome to the AnalystCave Excel Optimizer the AddIn that will optimize Excel for you! Excel Optimizer
Stay tuned for updates on this post!
I must say this idea has been popping in and out of my head for some time now – although it turned out the first working version of the Optimizer took me only a couple of hours to build. Often coming along Excel files that literally called for wrath from the Gods, I yearned for a time that Excel would include a “Fix Me” button – doing away with all the wrongs of the Workbook I was working on. Unfortunately this is (still) not the case and building your Excel file many of us have to be constantly aware of the dangers that lie ahead. What is more, many Excel users waste a lot of time not appreciating simple tips and tricks that can seriously enhance their Excel experience (like simply saving files in XLSB file format).

Optimize Excel with the Excel Optimizer

The Excel Optimizer is a simple AddIn to Excel that runs certain rules across your Workbook and points out the main pain-points or suggests certain improvements – all with performance in mind. Some rules can be implemented (I am hoping making all as such) while others will simply point out what needs to get done.

This is still a beta-work-in-progress-prototype so keep in mind that there may be some error/bugs here and there. What is more the Excel Optimizer current will only be able to suggest certain fixes (such as replacing some Excel functions with others). Depending on how popular this AddIn becomes I am planning to extend it to be able to actually implement most of the recommended tweaks. Optimizing Excel for best performance has become much easier.

See this video for a quick showcase of how the Excel Optimizer works.

Documentation

The Excel Optimizer consists of 2 features:

  • Optimize Excel – the main feature. Opens a window (screen below) that will allow you to run a rule-analysis on your current Workbook. Each rule with validate against different performance standards, rules also have different scopes. There are rules validating the format of the Workbook, validating worksheets and then there are the low-level rules that validate each and every formula. Using the settings button you can turn rules on/off or configure them to enhance your experience
  • Timer Full Calculation Rebuild – a simple macro that calculates the time needed to do a Full Rebuild Calculation of your Excel workbook. This comes in handy when wanting to set a baseline for the improvements you might want to implement

Excel Optimizer: AddInRibbon buttons
Excel Optimizer: AddInRibbon buttons

Below a screenshot of how the main window looks like:
Excel Optimizer: Main window
Excel Optimizer: Main window

Rules

Rules are what drive the Excel Optimizer. The AddIn has been built in a way to make adding/removing/enabling/disabling rules as easy as possible. Currently the Excel Optimizer introduces the following types of rules – many of most still cannot be implemented automatically (expect this in the near future):

Rule Scope Description
XLSB Workbook Checks if workbooks is in XLSB format if not suggests so
UsedRange Worksheet Validates the Used Range of each Worksheet to see if the Used Range is excessive and can be reduced for better performance and optimum file size
VLOOKUP Range Looks for exact (FALSE) VLOOKUPs. If found suggest replacing them with double approximate (TRUE) VLOOKUPs for best performance
Repeating formula Range Looks for exact repetitions of certain functions and suggest replacing them with a certain cell to minimize the need to recalculate the same function multiple times
Volatile functions Range Looks for volatile native Excel functions and suggests removing or replacing them
External links Range Looks for any external links used within cells in the entire Workbook and suggests removing them or reducing to a bare minimum
Array Formula Range Looks for Array Formulas within cells in the entire Workbook and suggests replacing them with regular Excel functions if possible
Error Range Looks errors within cells in the entire Workbook and suggests correcting them

Download

You can download the latest version here:

Do you have ideas for new rules? Any suggestions / wishes for additional features? Put your comment below or on my Facebook / Twitter page.