How to Count Cells In A Range

Earlier, we learned about how to count cells with numbers, count cells with text, count blank cells and count cells with specific criterias. In this article, we will learn about how to count all cells in a range in excel.
0036
There is no individual function in excel that returns total count of cells in a given range. But this doesn’t mean we can’t count all cell in excel range. Let’s explore some formulas for counting cells in a given range.
Using COUNTA and COUNTBLANK to Count Cells in a Range
*this method has problem.
As we know that t COUNTA function in excel counts any cell which is not blank. On the other hand COUNTBLANK function counts blank cell in a range. Yes, you guessed it write, we can add them to get total number of cells.
Generic Formula to Count Cells

=COUNTA(range)+COUNTBLANK(range)

Example
0037
Suppose, I want to to count total number of cells in range A1:B6. We can see that it has 12 cells. Now let’s use the above formula for counting the cell in given range.

=COUNTA(A1:B6)+COUNTBLANK(A1:B6)

This formula to get cell count in range returns the correct answer.
0038
The problem: If you read about COUNTA function, you’ll find that it counts any cell containing anything, even a formula that returns blank. COUNTBLANK function also counts any cell which is blank, even if it is returned by a formula. Now since both functions will count same cells, the returned value will be incorrect. So, use this formula to count cells, when you are sure that no formula returns blank value.
0039
Count cells in a range using ROWS and COLUMN Function
Now, we all know that a range is made of rows and columns. Any range has at least one column and one row. So, if we multiply rows with columns, we will get our number of cell in excel range. This is same as we used to calculate the area of a rectangle.
0040
Generic Formula to Count Cells

=ROWS(range)*COLUMNS(range)

Let’s implement this formula in above range to count cells.

=ROWS(A1:B6)*COLUMNS(A1:B6)

0041
This returns the accurate number of cells in range a1:B6. It doesn’t matter what values these cells hold.
How it works
It is simple. The ROWS function, returns count of rows in range, which is 6 in this case. Similarly, COLUMN function returns the number of columns in the range, which is 2 in this example. The formula multiplies and returns the final result as 12.
CountCells VBA Function to Count All Cells in a Range
In both of the above methods, we had to use two function of excel, and provide the same range twice. This can lead to be human error. So we can define a user defined function to count cells in a range. This is easy.
Press ALT+F11 to open VBA editor. Go to insert and click on module. Now copy below VBA code in that module.

Function CountCells(rng As Range)
CountCells = rng.CountLarge
End Function

Return to your excel file. Write CountCells function to count cells in a range. Provide range in parameter. The function will return the number of cell in the given range.

Let’s take the same example.

Write below formula to count cells in range A1:B6

=CountCells(A1:B6)

0042
How it works

Actually, CountLarge is method of range object that counts the cell in a given range. CountCells function takes the range from user as argument and returns the cell count in range using Range.CountLarge.
0044
This method is faster and easy to use. Once you define this function for counting cells in a range in excel, you can use it as many times as you want.

So yeah, these are the formulas to count cells in a range in excel. If you know any other ways, let us know in the comments section below.

Related Articles:

Count Cells that contain specific text

Count Unique Values In Excel

How to Use COUNT Function in Excel

Count Cells With Text

The COUNTIFS Function n Excel

COUNTIF in Excel 2016

Count table rows & columns in Excel

Popular Articles:

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube