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.

In order to demonstrate this tutorial, we are going to use the following dataset:

Here, in this dataset, we have the **name**, **age**, and **occupation** 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.

### 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 with 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:**

1. First, create a new column “**Count**“.

2. Then, type the following formula in **Cell** **E5**:

`<span style="font-size: 14pt;">=IF(COUNTA(B5:D5)>0,1,0)</span>`

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

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

5. Now, type the following formula in **Cell** **D13:**

`=SUM(E5:E11)`

6. Next, press **Enter**.

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

### 2. Using the IF and COUNTBLANK Functions to Count Rows with Data in Excel

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

1. First, type the following formula in **Cell** **E5**:

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

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

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

4. Now, type the following formula in **Cell** **D13**:

`=SUM(E5:E11)`

5. Next, press **Enter**.

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

### 3. Using the IF and COUNTIF Functions to Count Rows with Data

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

1. First, type the following formula in **Cell E5**:

`<code>`

**=IF(COUNTIF(B5:D5,””)=3,0,1)**

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

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

4. Now, type the following formula in **Cell** **D13**:

`=SUM(E5:E11)`

5. Next, press **Enter**.

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

### 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 similar 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:**

1. Type the following formula in **Cell D13**:

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

2. Then, press **Enter.**

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

#### 🔎Breakdown of the Formula

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

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

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

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

**5. 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})**

And it will return 4 in Cell D13.

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