Count Rows In Excel 6 Ways To Count Number Of Rows In Excel

How to Count the Number of Rows in Excel?

#1 – Excel Count Rows which has only the Data

#2 – Count all the rows that have the data

We know how to check how many rows contain the data quickly. But that is not the dynamic way of counting rows that have data. Instead, we need to apply the COUNTA function to count how many rows contain the data.

We can count the number of rows with data by selecting the range of cells in Excel. For example, take a look at the below data. We have a total of 10 rows (border inserted area). In this 10 row, we want to count exactly how many cells have data. Since this is a small list of rows, we can easily calculate the number of rows. But when it comes to the huge database, it is impossible to count manually. So, this article will help you with this. Firstly, we must select all the rows in Excel. It is not telling us how many rows contain the data here. Instead, look at the Excel screen’s right-hand side bottom, i.e., a status bar. Take a look at the red circled area. It says COUNT as 8, which means that out of 10 selected rows, 8 have data. Now, we will select one more row in the range and see what the count will be. We have selected 11 rows, but the count says 9, whereas we have data only in 8 rows. When we closely examine the cells, the 11th row contains a space. Even though there is no value in the cell and it has only space Excel will be treated as the cell which contains the data.

We have a total of 10 rows (border inserted area). In this 10 row, we want to count exactly how many cells have data. Since this is a small list of rows, we can easily calculate the number of rows. But when it comes to the huge database, it is impossible to count manually. So, this article will help you with this.

Take a look at the red circled area. It says COUNT as 8, which means that out of 10 selected rows, 8 have data.

Even though there is no value in the cell and it has only space Excel will be treated as the cell which contains the data.

We will apply the COUNTA function in the D3 cell.

So, the total number of rows containing the data is 8 rows. Even this formula treats space as data.

#3 – Count the rows that only have the numbers

Here we want to count how many rows contain only numerical values.

We can easily say 2 rows contain numerical values. Let us examine this by using a formula. We have a built-in formula called COUNT which counts only numerical values in the supplied range.

We will apply the COUNT function in cell B1 and select the range as A1 to A10.

The COUNT function also says 2 as a result. So, out of 10 rows, only rows contain numerical values.

#4 – Count Rows, which only has the Blanks

We can find only blank rows by using the COUNTBLANK function in Excel.

We have 2 blank rows in the selected range, which are revealed by the count blank function.

#5 – Count rows that only have text values

Remember, we do not have any straight in the COUNTTEXT function. Unlike in previous cases, we need to think differently here. We can use the COUNTIF functionCOUNTIF FunctionThe COUNTIF function in Excel counts the number of cells within a range based on pre-defined criteria. It is used to count cells that include dates, numbers, or text. For example, COUNTIF(A1:A10,”Trump”) will count the number of cells within the range A1:A10 that contain the text “Trump” read more with a wildcard character asterisk (*).

Here all the magic is done by the wildcard character asterisk (*). It matches any of the alphabets in the row and returns the result as a text value row. Even if the row contains numerical and text values, it will be treated as text value only.

#6 – Count all of the rows in the range

Now comes the important part. How do we count how many rows we have selected? One uses the Name Box in excel, which is limited while still choosing the rows. But how do we measure then?

We have a built-in ROWS formula, which returns how many rows are selected.

Things to Remember

Even space is treated as a value in the cell.If the cell contains numerical and text values, it will be treated as a text value.ROW may return the current row we are in, but ROWS will return how many are in the supplied range even though there is no data in the rows.

How to Count the Number of Rows in Excel?#

#1 – Excel Count Rows which has only the Data#

#2 – Count all the rows that have the data#

#3 – Count the rows that only have the numbers#

#4 – Count Rows, which only has the Blanks#

#5 – Count rows that only have text values#

#6 – Count all of the rows in the range#

Things to Remember#

Recommended Articles#