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

Get FREE Advanced Excel Exercises with Solutions!

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.


Excel Count Number of Cells in Range: 6 Easy Ways

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 the Enter button

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

Read More: How to Count If Cell Contains Number in Excel


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 the 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: How to Count Odd and Even Numbers in Excel


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


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.


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


Download Practice Book

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


Conclusion

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


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. 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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo