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.

**Table of Contents**hide

## 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.

**Read More: How to Use Different Types of COUNT Functions in Excel**

## 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.

**Read More: How to Count Numbers in a Cell in Excel**

## 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**.

**Read More: How to Find 5 Most Frequent Numbers in Excel**

## 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**.

**Read More: How to Count Unique Values Using Excel Formula**

## 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**.

**Similar Readings**

**How to Apply the COUNTIF Function in Excel with Multiple Criteria****How to Use COUNTIFS Function with Multiple Criteria in Excel****How to Count Number of Columns in Excel**

**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.