While working with a large amount of data, finding empty cells is a very common scenario. Sometimes, we can insert empty rows by mistake which have no data in them. As a result, it may create some problems in our calculation in Excel. So, we need to count those rows that have at least one non-empty cell. In this tutorial, we are showing you how to count rows with data in Excel with suitable examples and proper illustrations.

**Table of Contents**hide

## Download Practice Workbook

Download the following practice workbook.

## 4 Formulas to Count Rows with Data in Excel

Here, we are providing you with four formulas that you can implement in your dataset. We recommend learning and trying all of these to enrich your Excel knowledge.

### 1. Using the IF and COUNTA Functions to Count Rows with Data

This formula is a combination of both the **IF **and **COUNTA **functions. The **COUNTA function** counts all the non-empty cells. So, it can tell us whether we have data in those rows or not. Then, by using the **IF **function, we are giving those rows a boolean value of 1 (**TRUE**) if that row contains data or 0 (**FALSE**) if it doesn’t. In the end, we are adding up those counts to find all the rows with data.

**The Basic Syntax of This Formula:**

**=IF(COUNTA(range)>1,1,0)****Steps:**

- Here, in this dataset, we have the
**N****ame**,**A****ge**, and**O****ccupation**columns. Some rows are completely blank here. And some rows have non-empty cells. Now, our goal is to count all the rows which have at least one non-empty cell. That means we have to count rows with data. - First, create a new column “
**Count**“.

- Then, type the following formula in
**Cell****E5**:

`=IF(COUNTA(B5:D5)>0,1,0)`

- Press
**Enter**. After that, it will show**1**as it has data.

- Then, drag the
**Fill handle**icon over the range of cells**E6:E11**. After that, you will see all the values that indicate whether the rows have data or not.

- Now, type the following formula in
**Cell****D13:**

`=SUM(E5:E11)`

- Next, press
**Enter**.

As you can see, we have successfully counted all rows with data.

**Read More:** **How to Count Rows with Formula in Excel (5 Quick Methods)**

### 2. Combining COUNTBLANK Function with IF

Similar to the previous method, this is also a combination of two functions. Here, instead of the **COUNTA **function, we are going to use the **COUNTBLANK function**.

The **COUNTBLANK** function mainly** counts **all the** blank cells** in Excel. Here, every row has three columns. So, the **COUNTBLANK **function will count all the blank cells. If all three cells have no data, it will return 3. So, if it returns 3 that means there is no data in that row. As a result, our formula will return 0 otherwise 1.

**The Basic Syntax of This Formula:**

**=IF(COUNTBLANK(range)=3,0,1)****Steps:**

- First, type the following formula in
**Cell****E5**:

`=IF(COUNTBLANK(B5:D5)=3,0,1)`

- Press
**Enter**. After that, it will show**1**as this row has data.

- Then, drag the
**Fill handle**icon over the range of cells**E6:E11**. After that, you will see all the values that indicate the rows have data or not.

- Now, type the following formula in
**Cell****D13**:

`=SUM(E5:E11)`

- Next, press
**Enter**.

In the end, we are successful in counting all those rows with data.

**Read More:** **How to Count Rows with Multiple Criteria in Excel (6 Methods)**

### 3. Merging COUNTIF with IF Function

Now, this method is similar to the previous method. We are replacing the **COUNTBLANK function** with the **COUNTIF** **function**. The **COUNTIF function** counts cells based on criteria. Here, we are counting the cells in a row if it has no value. So, if it returns three that means there is no value in that row. After that, the **IF **function will return 0.

**The Basic Syntax of This Formula:**

**=IF(COUNTIF(range,criteria)=3,0,1)****Steps:**

- First, type the following formula in
**Cell E5**:

`=IF(COUNTIF(B5:D5,"")=3,0,1)`

- Press
**Enter**. After that, it will show**1**as it has data.

- Then, drag the
**Fill handle**icon over the range of cells**E6:E11**. After that, you will see all the values that indicate whether the rows have data or not.

- Now, type the following formula in
**Cell****D13**:

`=SUM(E5:E11)`

- Next, press
**Enter**.

Finally, by summing up those boolean values, we can quickly find the number of rows with data in them.

**Read More:** **How to Count Rows with Text in Excel (Easiest 7 Ways)**

**Similar Readings**

**Excel VBA to Count Rows with Data (4 Examples)****How to Count Rows with VBA in Excel (5 Approaches)****Excel VBA: Count Rows with Specific Data (8 Examples)****How to Count Rows in Selection Using VBA in Excel****Excel VBA: Count Rows in a Sheet (5 Examples)**

### 4. Count Rows with Data Using the SUMPRODUCT and MMULT Functions

In this method, we are combining the **SUMPRODUCT** and **MMULT (Matric Multiplication) **functions in order to count rows with data.

**The SUMPRODUCT function** returns the sum of the products of corresponding ranges or arrays. The default formula is multiplication, but addition, subtraction, and division are also achievable.

**The Basic Syntax of the SUMPRODUCT Function:**

**=SUMPRODUCT(array1, [array2], [array3], …)**On the other hand, **the MMULT function** returns the matrix product of two arrays. The outcome is an array with an equal number of rows as array1 and an equal number of columns as array2.

**The Basic Syntax of the MMULT Function:**

**=MMULT(array1, array2)**Here, array1 and array2 are those arrays that we want to multiply.

**The Basic Syntax of Our Formula:**

**=SUMPRODUCT((MMULT((array1=””)*1,array2<3)*1)****Steps:**

- Type the following formula in
**Cell D13**:

`=SUMPRODUCT((MMULT((B5:D11="")*1,{1;1;1})<3)*1)`

- Then, press
**Enter.**

In the end, our formula has successfully counted rows with data.

**🔎 Breakdown of the Formula:**

**Check if a cell Is empty or not**

The** B5:D11=”” **means if the cell is empty or not. For example, it will return** {FALSE, FALSE, FALSE}** for **row1**.

**Convert Boolean values into numbers**

Now,** (B5:D11=””)*1 **will return all those booleans into Zero or One. For **row1**, it will return** {0,0,0}**.

**Add values row-wise**

The **MMULT **function is exceptional for summing values row by row, however, it can not handle boolean values. The function returns an array of values.

**MMULT((B5:D11=””)*1,{1;1;1})**

For **row1**, our array is **{0,0,0}**. So, our sum will be** 0**.

For **row2**, our array is **{0,1,0}**. So, our result is **3**.

**Check if each value in the array is smaller than 3**

**MMULT((B3:D14=””)*1,{1;1;1})<3**

If there are 3 empty values, there are no data in that row. So, by using this formula we are checking whether the row is blank or not.

For **row1**, our array was **{0,0,0}**. So, the result will be **TRUE**.

For **row2**, our array is **{0,1,0}**. So, our result is **TRUE**.

For** row3**, our array is** {1,1,1}**. So, our result is **FALSE**.

**Count rows with data**

**SUMPRODUCT((MMULT((B5:D11=””)*1,{1;1;1})<3)*1)**

In order to sum the array of boolean values, we have to multiply with **1** to convert them to **1** or** 0** (zero). **TRUE** = **1** and **FALSE** = **0**.

After that, it will become:

**SUMPRODUCT({1; 1; 0; 1; 0; 0; 1})**

As a result, it will return **4** in Cell **D13**.

**Read More:** **How Excel Count Rows with Value (8 Ways)**

## Conclusion

To conclude, I hope this tutorial will help you count rows with data effectively. Download the practice workbook and try these methods yourself. Feel free to give any feedback in the comment section. Your valuable feedback keeps us motivated to create content like this. And don’t forget to check our website** Exceldemy.com **for various Excel-related problems and solutions.

## Related Articles

**Count Visible Rows in Excel (3 Suitable Methods)****How to Count Filtered Rows in Excel with VBA (Step-by-Step Guideline)****Excel VBA: Count Rows in Named Range (4 Methods)****How to Count Rows with Data in Column Using VBA in Excel (9 Ways)****Count Rows in Group with Pivot Table in Excel (Step-by-Step Guideline)**