Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

# How to Count Rows with Data in Excel (4 Suitable Formulas)

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.

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

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

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

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

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

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts 