Md. Sourov Hossain Mithun
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.
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.
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.
Similar Readings
- How to Count Odd and Even Numbers in Excel (3 Easy Ways)
- How to Count If Cell Contains Number (Easiest 7 Ways)
- Count Cells That Are Not Blank in Excel (6 Useful Methods)
- How to Count Filled Cells in Excel (5 Quick Ways)
- How to Count Cells with Specific Text in Excel (Both Case Sensitive and Insensitive)
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.
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 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.
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
