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

**Download Practice Workbook**

You can download the practice workbook that we have used to prepare this article.

**5 Quick Methods to Count Rows with Formula in Excel**

**1. Use Formula with 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. Apply Excel ****Formula with ****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. Insert Formula with 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**.

**Similar Readings:**

**Excel VBA to Count Rows with Data (4 Examples)****How to Count Rows with Data in Excel (4 Formulas)****How Excel Count Rows with Value (8 Ways)**

**4. Use Excel ****Formula to Count Rows with ****IF and COUNTIF Functions in Excel**

Likewise, in **Method 2** and** Method 3**, now 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
**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. Combination of 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 is:

**–(B5:D10=80):**This logical criterion create**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**.

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