How to Count the Number of Cells in a Range in Excel (6 Easy Ways)

Method 1 – Using the Excel COUNTA Function 

Below, 3 columns and 8 rows show sales of multiple venders in different regions.

Steps:

• Select cell
• Enter the following formula in the cell:

• Press Enter.

Method 2 – Inserting the COUNT Function 

In the below dataset, we will count the number of cells in the Sales column.

Steps:

• Select cell
• Enter the following formula in the cell:

• Press Enter.

We see 7 cells under the “Sales” header.

Method 3 – Entering the COUNTBLANK Function

In the below dataset, some region names are removed to show how to count blank cells using the Countblank Function.

Steps:

• Select cell
• Enter the following formula in the cell:

• Press Enter.

The number of blank cells is 3.

Method 4 – Applying the COUNTIFS Function 

In the below dataset, there are two ranges of sales. The Countifs Function is used to count cells that meet specified criteria.

Step 1:

• Select cell
• Enter the following formula in the cell:

• Press Enter.

Step 2:

• Use the Fill Handle to copy the formula to the next criteria range.

Method 5 – Combining the COLUMNS and ROWS Functions 

In the below dataset, we will use multiple functions to count total cells in a range. We will use the columns and rows functions.

Steps:

• Select cell
• Enter the following formula in the cell:

• Press Enter.

Method 6 – Embedding Excel VBA

In the below dataset, we’ll use Excel VBA to count the total cells.

Step 1:

• Right-click on the sheet title name.
• Select View Code from the context menu.
• A Microsoft VBA window will open up.

Step 2:

• Enter the following formula in the cell:

Option Explicit
Sub CountCells()
End Sub
Function CountAllCells(rng As Range)
CountAllCells = rng.CountLarge
End Function

Step 3:

• Press the Play button to run the codes.

A new window will appear – “Macros”

• Press Run.
• Close the VBA

Step 4:

• Select cell
• Enter the following formula into the cell:

• Press Enter.

In the image below, we have our total cell numbers with our new function.

Download Practice Book

Download the Excel workbook to practice.

<< Go Back to Count Cells | Formula List | Learn Excel