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