What Is Count Rows In Excel?
The different methods to count rows in Excel enable one to determine the total number of rows in a given dataset. Users can count Excel rows containing data, numeric values, texts, rows in a range and blank rows while analyzing financial and statistical data.
For example, the first table lists grocery items and their quantities.
And if we must count the total rows of grocery items and quantity values and display the output in cells F2:F3. Then, we can apply the Excel COUNTA function as the formula to count rows in Excel cells F2:F3.
The COUNTA() as the formula to count rows in Excel cells F2 and F3 calculates the total number of rows containing data in the specified cell range. And it returns the output as a number.
Table of contents
Key Takeaways
- The count rows in Excel techniques evaluate the total number of rows in the specified cell range.
- Users can count Excel rows based on data, specific data formats, blanks, and in a range while working with sales, marketing, and financial datasets.
- We can check the Count field in the Status Bar to determine the rows count in a chosen cell range. And we can use the Excel inbuilt functions COUNTA, COUNT, COUNTBLANK, COUNTIF, and ROWS to count rows with data, numbers, blanks, texts, and total rows, respectively.
How To Count The Number Of Rows In Excel?
Let us see how to automatically count rows in Excel and use different inbuilt functions to obtain the Excel row counts based on specific requirements.
#1 – Excel Count Rows Which Has Only The Data
We can consider the following method as the shortcut to count rows in Excel that contain only data:
- Ensure the dataset contains accurate and valid data.
- Select the cell range in the column where we must count the total rows containing only data.
- Check the Excel Status Bar for the Count value, showing the number of non-empty rows in the chosen column range.
Basic Example
The image below shows an employee’s details in column C.
And the requirement is to count the total number of non-empty rows in the cell range C2:C8.
Then, here is how to refer to the Status Bar as the shortcut to count rows in Excel containing data.
- Select the cell range C2:C8.
Once we choose the required cell range, Excel will show the count of non-empty rows in the chosen range in the Status Bar at the bottom-right corner of the worksheet.
In the above example, the chosen cell range contains seven cells, and five appear non-empty. However, the Count value in the Status Bar is 6.
The reason is that cell C4 is indeed empty. But cell C5 contains two space characters, making it a non-empty cell.
And, since six cells out of the seven cells in the chosen range are non-empty, Excel shows the required number of total rows containing data as 6 in the Status Bar.
Thus, we can use the above method to automatically count rows in Excel that are non-empty.
#2 – Count All The Rows That Have The Data
The steps to use the COUNTA() to count all the rows that have the data are as follows:
- Select the cell range in the required column, where we must count all the non-empty rows.
- Select a target cell to display the output.
- Type =COUNTA( in the cell. [Alternatively, type =C or =CO and double-click the COUNTA function from the Excel suggestions.]
- Enter the required cell range as the function argument and close the bracket.
- Press Enter to view the COUNTA() output.
Basic Example
The table below lists students’ names.
Here is how to count all the rows with the data in the cell range A2:A16 using COUNTA(). Assume the target cell is B18.
- Step 1: Choose cell B18 and enter the COUNTA().
=COUNTA(A2:A16)
- Step 2: Press Enter to view the COUNTA() output in the target cell.
The COUNTA() returns the total count of non-empty cells in the specified range.
In the above example, the total number of rows in the chosen range is 15. And the total number of non-empty cells in the chosen range is 10, which the COUNTA() returns.
#3 – Count The Rows That Only Have The Numbers
The steps to use the Excel COUNT function to count the rows that only contain numbers are as follows:
- Select the cell range in the required column, where we must count all the rows containing numbers.
- Select a target cell to display the output.
- Type =COUNT( in the cell. [Alternatively, type =C or =CO and double-click the COUNT function from the Excel suggestions.]
- Enter the required cell range as the function argument and close the bracket.
- Press Enter to view the COUNT() output.
Basic Example
The following table lists numeric values.
Here is how to count all the rows containing numbers in the cell range A2:A12 using COUNT(). Assume the target cell is B14.
- Step 1: Choose cell B14 and enter the COUNT().
=COUNT(A2:A12)
- Step 2: Press Enter to view the COUNT() output in the target cell.
The COUNT() returns the total count of cells containing numbers in the specified range.
In the above example, the total number of rows in the chosen range is 11. And the values in the non-empty cells in the chosen range are numbers, though in different valid number formats. We can confirm the same by clicking each cell and checking the value in the Formula Bar.
And hence, the COUNTA() returns the total number of cells containing numbers in the chosen range, 9.
#4 – Count Rows, Which Only Has The Blanks
The steps to use the Excel COUNTBLANK function to count the rows that only have the blanks are as follows:
- Select the cell range in the required column, where we must count all the rows containing blanks.
- Select a target cell to display the output.
- Type =COUNTBLANK( in the cell. [Alternatively, type =C or =CO and double-click the COUNTBLANK function from the Excel suggestions.]
- Enter the required cell range as the function argument and close the bracket.
- Press Enter to view the COUNTBLANK() output.
Basic Example
The following table lists the top US tech companies.
Here is how to count all the blank rows in the cell range A2:A11 using the COUNTBLANK(). Assume the target cell is B13.
- Step 1: Choose cell B13 and enter the COUNTBLANK().
=COUNTBLANK(A2:A11)
- Step 2: Press Enter to view the COUNTBLANK() output in the target cell.
The COUNTBLANK() returns the total count of blank cells in the specified range.
In the above example, the total number of rows in the chosen range is 10. However, the rows 4 and 7 to 9 are blank.
And hence, the COUNTBLANK() returns the total number of blank rows in the chosen range as 4.
#5 – Count Rows That Only Have Text Values
Excel does not offer an inbuilt function to count the rows containing only text values directly. However, we can use the Excel COUNTIF function with the Wildcard character, ‘*’, as the criteria to achieve the required outcome.
And the steps are as follows:
- Select the cell range in the required column, where we must count all the rows containing only text values.
- Select a target cell to show the output.
- Type =COUNTIF( in the cell. [ Alternatively, type =C or =CO and double-click the COUNTIF function from the Excel suggestions.]
- Enter the required cell range and the Wildcard character, ‘*’, as the two function arguments separated by a comma, and close the bracket.
- Press Enter to view the COUNTIF() output.
Basic Example
The following table lists a set of item codes.
The item codes, with the phrase “TTL_” suffixing the numeric values, are valid text values.
So, here is how to count all the rows containing valid item codes in the cell range A2:A15, using the COUNTIF() and the Excel Wildcard character, ‘*’. Assume the target cell is B17.
- Step 1: Choose cell B17 and enter the COUNTIF().
=COUNTIF(A2:A15,”*”)
- Step 2: Press Enter to view the COUNTIF() output in the target cell.
The COUNTIF() returns the total number of cells in the given range that satisfy the specified criteria.
In the above example, the total number of rows in the chosen range is 14.
However, as the Wildcard character, ‘*’, is the criteria argument in the COUNTIF(), the function checks for cells containing text values of any length.
And hence, as the criterion holds in cells 2 to 6, 10, 11, 13, and 15, the COUNTIF() returns the total number of rows containing text values in the chosen range as 9.
#6 – Count All Of The Rows In The Range
The steps to use the Excel ROWS function to count all the rows in a given range are as follows:
- Select the cell range in the required column, where we must count all the rows.
- Select a target cell to display the output.
- Type =ROWS( in the cell. [Alternatively, type =R or =RO and double-click the ROWS function from the Excel suggestions.]
- Enter the required cell or array range as the function argument and close the bracket.
- Press Enter to view the ROWS() output.
Basic Example
The image below shows column A containing the list of the top 15 US states by population.
The requirement is to convert the above dataset into an Excel Table. And then, count the total rows in the given list, including both blank and non-empty ones. Assume the target cell is D1.
Then, here is how to count rows in Excel Table.
- Step 1: Choose the cell range A1:A16 and select Insert → Table.
The Create Table window will open, with the fields updated as shown below.
Click OK to obtain the required Excel Table.
- Step 2: Click on a cell in the Excel Table to enable the Design tab in the ribbon.
And check the Total Row check box to view the total number of rows containing values in cell A17.
- Step 3: Cell A17 shows the Excel SUBTOTAL function output, indicating the total number of non-empty rows in the Excel Table. However, the total rows count must also include the empty rows.
So, click the cell A17 drop-down button and choose More Functions.
The Insert Function window opens, where we must select the ROWS function.
Clicking OK in the Insert Function window will open the Function Arguments window to insert the ROWS() in cell A17.
- Step 4: Update the Array field in the Function Arguments window as the cell range A2:A16 in the Excel Table.
Click OK in the Function Arguments window to view the total number of rows in the Excel Table in the target cell A17, 15.
[ Alternatively, we can select cell D1 and enter the ROWS() to count rows in Excel Table created previously.
Next, enter the cell range A2:A16, where we must count the total rows. And then, close the bracket.
Finally, press Enter.
Thus, we will obtain the total count of all rows in the given Excel Table in the target cell D1, 15.]
Important Things To Note
- When we count rows in Excel, even a single space character in a cell gets counted as a value.
- Please avoid using the COUNT functions for counting rows when cells are merged, as they might return incorrect values.
- The Total Row option in the Design tab does not include the specific column’s header in the Excel Table when displaying the total rows count.
Frequently Asked Questions (FAQs)
We can count unique rows in Excel using the COUNTIF() within the SUMPRODUCT().
For example, the following table lists values in various valid data formats.
And the requirement is to count the unique rows in the given dataset. Assume the target cell is B10.
Then, here is how to use the COUNTIF() and SUMPRODUCT() in the target cell to achieve the required output.
• Step 1: Choose cell B10, enter the following formula, and press Enter.
=SUMPRODUCT(((A2:A8<>””)/COUNTIF(A2:A8, A2:A8&””)))
The above formula ignores blank spaces.
First, the numerator is an array of seven TRUEs since the specified range contains no blank cells.
Next, the criteria in the COUNTIF() is the array {“50″;”50″;”45097″;”Samsung Galaxy Z Flip 4″;”45097″;”Samsung Galaxy Z Flip 4″;”0.5”}.
And then, the COUNTIF() counts the appearances of each array value in the specified cell range and returns the array {2,2,2,2,2,2,1}. The values in the array indicate that the first six values appear twice, and the last value appears once in the given dataset.
So, the SUMPRODUCT() input becomes:
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}/{2,2,2,2,2,2,1}.
And the division results in the array {0.5;0.5;0.5;0.5;0.5;0.5;1}, since the TRUE value equals 1.
Finally, the SUMPRODUCT() adds all the values in the array to return the number of unique rows as 4.
Furthermore, cells A2 and A3 contain the same value but in the Currency and Accounting data formats. Next, cells A4 and A6 contain the same date value but in different date formats. On the other hand, cells A5 and A7 contain the same text and cell A8 contain a percentage value.
So, the total number of unique values in the given dataset is 4, corroborating the obtained count of unique rows.
We can count filtered rows in Excel using the following steps:
1) Choose the required column containing the source cell range and select Data → Filter to apply the filter to the column.
2) Select a target cell to display the output.
3) Enter the following SUBTOTAL().
=SUBTOTAL(3,Cell_Range)
Or
= SUBTOTAL(103,Cell_Range)
The first argument, 3 and 103, indicates COUNT as the chosen function to apply to the cell range in the required column, specified as the second argument.
And, while the first formula counts with hidden rows, the second counts without hidden rows.
4) Press Enter to execute the SUBTOTAL() in the target cell.
5) Click the given column’s filter button and select the values to filter for display. And once we click OK, the filtered values show in the sheet, with the SUBTOTAL() in the target cell displaying the count of filtered rows in the chosen cell range.
We can count rows in Excel with multiple criteria using the following inbuilt functions:
• COUNTIF() with multiple text values as criteria.
• COUNTIF() with multiple date values as criteria.
• COUNTIFS() based on multiple conditions.
• SUMPRODUCT() with AND criteria.
• SUMPRODUCT() with OR criteria.
Download Template
This article must be helpful to understand the Count Rows In Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Count Rows In Excel. We learn doing it using status bar, COUNTA, COUNT, COUNTBLANK, COUNTIF & ROWS functions, with examples. You can learn more from the following articles –
Leave a Reply