Excel Count Number of Cells in Range (6 Easy Ways)

Counting the number of cells is a basic task in MS Excel. There are a lot of methods to do it. In this article, I’ll show 6 easy methods including VBA to count the number of cells in a range in Excel.


Download Practice Book

Download the Excel workbook that we’ve used to prepare this article.


6 Easy Ways to Count Number of Excel Cells in Range

Method 1: Use Excel COUNTA Function to Count Number of Cells in a Range

Let’s get introduced to our dataset first. I have used 3 Columns and 8 Rows to show some salespersons’ sales in different regions.

Method 1: Use Excel COUNTA Function to Count Number of Cells in a Range

In this method, I’ll show how to use the COUNTA function to count the number of total cells in the data range. The COUNTA function is used to count cells that are not empty.

Steps:

➤ Activate Cell B12

➤ Type the formula given below:

=COUNTA(B5:D11)

➤ Hit Enter button

Here we’ve got our total cell numbers of 21.

Read More: Excel Count Cells with Numbers (5 Simple Ways)


Method 2: Insert COUNT Function to Count Number of Cells in a Range in Excel

The COUNT function is used to count cells that contain numbers. In this method, I’ll apply it to count the number of cells in the Sales column.

Steps:

➤ Activate Cell D12

➤ Type the formula given below:

=COUNT(D5:D11)

➤ Now just hit Enter button.

And we see that there are 7 cells under the “Sales” header.

Method 2: InsertUse COUNT Function to Count Number of Cells in a Range in Excel

Read More: Excel Formula to Count Cells with Text


Method 3: Enter COUNTBLANK Function to Count Number of Blank Cells in  a Range

In our dataset, I have removed some region names to show how to count blank cells. For that, we’ll use the COUNTBLANK function. This function is used to count cells that are empty.

Steps:

➤ Activate Cell C12

➤ Type the formula given below:

=COUNTBLANK(B5:D11)

➤ Then just hit Enter button.

And the counted number of blank cells is 3 here.

Method 3: Enter COUNTBLANK Function to Count Number of Blank Cells in a Rang


Similar Readings


Method 4: Apply COUNTIFS Function to Count Number of Cells with Conditions in Excel

For this method, I have modified my dataset. I have set two ranges of sales. And we’ll use the COUNTIFS function here. This function is used to count cells that meet some specified criteria.

Step 1:

➤ Activate Cell H5

➤ Type the formula given below:

=COUNTIFS($D$5:$D$11,">="&F5,$D$5:$D$11,"<="&G5)

➤ Now press the Enter button.

Method 4: Apply COUNTIFS Function to Count Number of Cells with Conditions in Excel

Step 2:

➤ After that, use the Fill Handle to copy the formula to the next criteria range.

Read More: How to Count Blank Cells in Excel with Condition (3 Methods)


Method 5: Combine COLUMNS and ROWS Functions to Count Total Cells in a Range

Here we’ll use multiple functions to count total cells in a range. The functions are COLUMNS and ROWS functions. The COLUMNS function gives the count of columns in a given reference. The ROWS function is used to provide the number of rows in a given reference or array. By multiplying the result of those two functions we’ll get the total cell numbers.

Steps:

➤ Activate Cell G5

➤ Type the formula given below:

=ROWS(B5:D11)*COLUMNS(B5:D11)

➤ Then press the Enter button and get the result.

Method 5: Combine COLUMNS and ROWS Functions to Count Total Cells in a Range


Method 6: Embed Excel VBA Codes to Count Total Cells in a Range

Let’s do the operation in an interesting way. We’ll use Excel VBA in this method to count the total cells. At first, by using VBA we’ll make a new user-defined function named “CountAllCells” then we’ll apply it.

Step 1:

Right-click your mouse to the sheet title name.

➤ Select View Code from the context menu.

A Microsoft VBA window will open up.

Method 6: Embed Excel VBA Codes to Count Total Cells in a Range

Step 2:

➤ Type the codes given below:

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 named “Macros”

➤ Then press Run.

➤ Close the VBA window.

Our new function is ready now.

Step 4:

➤ Activate Cell F5

➤ Then type the formula as follows:

=CountAllCells(B5:D11)

➤ Hit the Enter button.

Now see the image below, we have got our total cell numbers with our new function.


Conclusion

I hope all of the methods described above will be good enough to count the number of cells in range. Feel free to ask any questions in the comment section and please give me feedback


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo