In this tutorial, I will explain some methods to count the number of rows using Excel Formula. Usually, we can get the row count by simply clicking the column headers. However, Excel has several options available to count rows that contain values. For example, we can use some functions and combinations of functions to count rows in Excel. So, let’s explore the methods.
1. Using the ROWS Function to Count Rows in Excel
There is a built-in function in Excel to count row numbers. We can get the row count of a range by simply applying the ROWS function formula. For instance, we have a dataset (B5:D10) containing several people’s biographical data. So, let’s find the row count of this dataset.
Steps:
- First, type the below formula in Cell D12.
=ROWS(B5:D10)
Here, the ROWS function returns the number of rows in the array: B5:D10.
- Next, Hit Enter.
- As a result, you will get the number of rows that contain values.
2. Applying IF and COUNTA Functions to Count Rows
Sometimes, large datasets contain blank rows. In that case, you may need to count those rows which contain data. In such situations, we can make formulas using Excel functions. For instance, we can get a formula using the combination of IF and COUNTA functions. Let’s add an extra column ‘Count’ in our existing dataset for ease of calculation.
Steps:
- Initially, we will check whether rows in our dataset contain values or not. To do that, type the below formula in Cell E5.
=IF(COUNTA(B5:D5)>0,1,0)
Here, the COUNTA function counts the number of non-empty cells in the range B5:D5. On the other hand, if the result of the COUNT formula is greater than 0, the IF function returns 1, otherwise, it returns 0.
- Next, press Enter and we will get the below result for Row 5. Later, drag down the Fill Handle (+) to copy the formula to the rest of the cells.
- Then, we will get the following count for each of the rows.
- Now, we will use the SUM function, to get the total number of the rows having data. So, type the following formula in Cell D12.
=SUM(E5:E10)
- After that, hit Enter.
- Finally, here is the total count of rows.
3. Inserting IF and COUNTBLANK Functions to Count Rows
Similar to Method 2, now we will use the combination of IF and COUNTBLANK functions to get the formula to calculate the total number of data containing rows in a dataset.
Steps:
- First, type the following formula in Cell E5.
=IF(COUNTBLANK(B5:D5)=3,0,1)
Here, the COUNTBLANK function counts the number of blank cells in the range B5:D5. In our dataset, we have three columns in each row. So, if all the cells are blank, the COUNTBLANK function will return 3. Later, the IF function will return 0 if the row is blank, otherwise 1.
- Next, press Enter. As a consequence, we will get the below result for each row.
- Later, similar to Method 2, we will sum up the above result to get the total non-empty row count of our dataset (B5:D10). Type the below formula in Cell D12 and press Enter.
=SUM(E5:E10)
- In conclusion, we found the total row count which is 5.
4. Using IF and COUNTIF Functions to Count Rows
Likewise, in Method 2 and Method 3, we will use the COUNTIF function along with the IF function to create the formula to get the non-empty row count.
Steps:
- First, type the below formula in Cell E5.
=IF(COUNTIF(B5:D5,"")=3,0,1)
Here, the COUNTIF function counts the number of cells based on criteria. As we will need to find a blank cell in each row, the COUNTIF function counts blanks (“”) in the range B5:D5. When all three cells are blank in a row, the IF function returns 0, otherwise 1.
- Consequently, after pressing the Enter key, we will get the below result.
- Subsequently, like before, we will add these individual counts of E5:E10 to get the total row count. So, type the formula in Cell D12.
=SUM(E5:E10)
- Then, hit Enter.
- Lastly, we will get the following row count which is 4.
5. Combining MMULT, TRANSPOSE, COLUMN, and SUM Functions to Count Rows
Sometimes, in a large dataset, we need to count the number of rows with a specific value. For instance, we have a dataset containing the test scores of students. Now, we will find out how many rows in our dataset have the score ‘80’. To serve our purpose, we will use the combination of the MMULT, TRANSPOSE, COLUMN, and SUM functions.
Steps:
- Initially, type the below formula in Cell D12.
=SUM(--(MMULT(--(B5:D10=80),TRANSPOSE(COLUMN(B5:D10)))>0))
- Then hit Enter.
- Finally, we have found that 5 rows contain the value 80.
🔎 How does the Formula Work?
We are looking for the score ‘80’ in the dataset. The toughest part here is that the score ‘80’ can be present in any of the columns of the dataset and it can be in more than one column of the same row. So, the logical criteria used here are:
- –(B5:D10=80): This logical criterion creates TRUE/FALSE results for each of the values in the range. Then, the double negative forces each TRUE/FALSE values to 1 and 0 and thus generate the below array:
{0,0,1;0,1,0;1,0,0;0,1,0;1,0,0;0,0,0}
- TRANSPOSE(COLUMN(B5:D10)): Now, the COLUMN function is used to generate a numeric array of the right size. The COLUMN function returns the 3-column array {2,3,4}, and the TRANSPOSE function changes this array to the 3-row array {2;3;4}.
- –(MMULT(–(B5:D10=80),TRANSPOSE(COLUMN(B5:D10)))>0): The MMULT function then runs and returns a 6 x 1 array result:
SUM(–({4;3;2;3;2;0}>0))
Now, we check for non-zero entries with > 0 and again force TRUE/FALSE to 1 and 0 with a double negative to get a final array inside the SUM function.
Finally, in the final array, 1 represents a row where the logical test returned TRUE. The summation returned by the SUM function is the count of rows (5) that contain the value 80.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
Conclusion
In the above article, I have tried to discuss the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.
Excel Count Rows: Knowledge Hub
- Count Visible Rows in Excel
- Use Excel to Count Rows with Value
- Count Rows with Text in Excel
- Count Rows with Multiple Criteria in Excel
- Count Filtered Rows with Criteria in Excel
<< Go Back to Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
