Updated August 21, 2023
Introduction to Row Count in Excel
Excel is a strong tool for handling data and performing complex calculations thanks to its numerous built-in functions. One such important Excel function is the Row Count feature, which lets you determine the total number of rows in a dataset immediately and easily. Simply put, the Row Count in Excel measures the total number of rows in a range that contains some value or content.
In this article, we will understand the utility of the following functions to improve data analysis capabilities:
- ROWS()
- COUNTA()
- COUNT()
- COUNTBLANK()
- COUNTIF()
Before we look at examples of functions, here’s a simple trick to count rows that contain data.
If we want to measure the rows which contain data, select all the cells of the first column by clicking on the column header. It will display the row count on the status bar in the lower right corner.
Let’s take some values in the Excel sheet to demonstrate this.
If you select the entire column which contains data and click on the column label for counting the rows, it will show you the row count. Refer to the below screenshot:
Let us now understand the Syntax of these 6 functions for counting the rows.
- The ROW() function gives you the row number of a particular cell.
- The ROWS() function gives you the count of rows in a range.
- The COUNT() function in Excel gives the number of cells containing numerical values in a range. It ignores text values, logical values, and empty cells.
- The COUNTA() function in Excel counts all the cells in a range that contain any type of data, including numbers, text, logical values, and errors. The COUNT function is useful to count only the cells containing numbers, but COUNTA also counts all non-blank cells in a range regardless of their data type.
- The COUNTBLANK() function in Excel gives the number of empty cells present in a range.
- The COUNTIF() function in Excel gives the number of cells that meet particular criteria or conditions in a range.
How to Count the Rows in Excel?
Let’s take some examples to understand the usage of ROWS functions.
Example #1: Using the ROWS Function
Purpose: To calculate the number of rows for the entire data
We have the following information about employees and want to calculate the number of rows in this data.
For this, we will apply the ROWS function in cell C18, as shown in the below screenshot:
This process returns the number of rows containing the data in the supplied range, i.e., A2:E16.
This way, we can find the number of rows for a given data.
Example #2: Using the COUNTA Function
Purpose: To calculate the rows having data
In Excel, counting the number of rows containing data while ignoring any empty rows is often necessary.
For example, consider a table withstudents” academic information in 10 rows, but some may contain empty cells or blank spaces.
As we can see in the above dataset, certain information is absent.
As per the screenshot below, we will apply the COUNTA function for counting the rows containing data concerning the “”Subjec”” column.
Here, C7 & C10 are blank rows. So when selecting the data in the C column from C2:C11, Excel will ignore those empty cells and consider only the ones with data.
The final result is given below:
Only 8 cells out of 10 contain data, while 2 are blank.
This method is useful for small tables where you can manually count the number of rows, but this approach is not feasible when dealing with large databases.
If a cell contains only a space or is empty, Excel will still count it as a cell with data. So, to count only those rows with actual data, you must manually check each cell in the selected range.
Example #3: Using the COUNT function
Purpose: To calculate rows with numerical data
The COUNT function can be useful to count only those rows that contain numerical values in Excel. Consider the below raw data with mixed data.
To count the rows containing numbers, you can apply the COUNT function to the data. In this example, we will use cell B13 to apply the formula.
Select the range A1 to A12 and enter the formula =COUNT(A1:A12) in cell B13.
Apply the function like the below screenshot:
Press ENTER key here, and it will return the count of rows containing only numbers.
The final result is as below:
The COUNT function will return the value 6, indicating that only 6 rows contain numerical values out of the selected range of 12.
This method is useful when counting only those rows with numerical values, not text or other data types.
Example #4: Using the COUNTBLANK Function
Purpose: To calculate the rows that have no data
Sometimes data contains only blank cells in Excel. In such situations, one can use the COUNTBLANK function.
The syntax for the COUNTBLANK function is:
=COUNTBLANK(range)
In this example, the product for March month is missing in the column” “Produc”” (B1 to B10). So, let’s see how we can count the number of rows that have only blank cells using the following formula:
=COUNTBLANK(B1:B10)
We will select the “”produc”” range from the dataset as per the below screenshot:
This formula will help to count the number of rows in the range B1 to B10 that contain only blank cells and return the result in B12.
Press ENTER key, which will give you the count of rows containing data in the passing range.
The final result is as below:
Here, the product information for March was missing, i.e., B8 was the only cell with missing data. So, Excel gave the result as 1.
Example #5: Using the COUNTIF Function
Purpose: To calculate the rows with text only
To count the rows with only text values, use the COUNTIF function in Excel with a wildcard character asterisk (*). We will consider the data from example 3 and count the rows containing text only.
Step 1: Start by dragging and selecting the data from which you want to count the rows. In this case, select the range A1:A12.
Step 2: In a blank cell, B13, enter the formula =COUNTIF(range” “” “) where” “rang”” is the cell range you selected in step 1.
The asterisk (*) acts as a wildcard character that matches any sequence of characters in a text value.
So now, for counting the rows, we will apply the function shown in the below screenshot:
Step 3: Hit the Enter key, and it will return the count of rows having data.
The result is below:
Here, only 4 cells contain text, while others are blank or contain numbers.
Things to Remember About Row Count in Excel
- When you click on the column heading for counting the rows, it tells you how many rows have information.
- You can customize the result appearing on the status bar at the bottom by right-clicking on it and selecting different options.
- Merging cells in Excel can cause issues when counting rows using functions like COUNT. In such situations, it is advisable to avoid merging cells to get an accurate row count.
- When working with tables, the first row is usually a header row that should not be counted as a data row.
Frequently Asked Questions
Q1. How do I count rows with text in Excel?
Answer: The COUNTIF function in Excel can be useful for ascertaining the number of text cells. The COUNTIF function helps you to count cells in a range that meets some conditions.
Solution:
Choose cell B13 to display the result.
Enter the formula “=COUNTIF(range, criteria)” in B13. Follow the below steps for the example of counting cells with text from the above screenshot with data including names of differe” t nations along with som” figures: “”rang”” refers to the array of cells within which you want the function to act, i.e., A1 to A12, and “”criteri”” refers to the condition, i.e., *. The ““”” in the formula means” not equal to blank”” the COUNTIF function will count all the cells that contain text.
- Press enter to see the number of cells containing text only.
Here, there are 7 cells containing text, i.e., names of nations. Therefore, Excel returned the result as 7.
Q2. What is the use of the ROWS function in Excel?
Answer: The ROWS function is useful when you need to know the number of rows for further calculations or data analysis in a range. You can use the result of the ROWS function in other formulas, such as INDEX, MATCH, and OFFSET, to manipulate data in the range.
Note that the ROWS function only counts the number of rows in a range, not the number of cells. Use the COUNT function instead to count the number of cells in a range containing specific data, including blank cells.
Q3. What is countif in Excel?
Answer: COUNTIF is an Excel function that counts the number of cells that meets a particular criterion. The COUNTIF function count cells based on a condition, such as how many cells contain a specific text string or value.
The COUNTIF function is a very useful tool for data analysis. It is common in various applications, such as tracking inventory, analyzing survey data, and counting specific occurrences in a dataset.
Recommended Articles
This article has been a guide to Row Count in Excel. Here we discussed How to Count the Rows, practical examples, and a downloadable Excel template. You can also go through our other suggested articles–
