How to Count Rows with Data in Excel (4 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.


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:

dataset for excel count rows with data

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

create count column to count rows with data

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

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

function to count rows with data

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

showing count of the row

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.

drag the fill handle to copy the formula

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

=SUM(E5:E11)

Sum function to count rows with data

6. Next, press Enter.

result of excel count rows with data

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)

Countblank formula with IF function to count

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

result after typing the formula

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.

count of all the rows with data or no data

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

=SUM(E5:E11)

adding all the counts

5. Next, press Enter.

excel count rows with data result

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)

typing COUNTIF and IF function combinely

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

value of first row with 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.

drag fill handle to copy the formula

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

=SUM(E5:E11)

adding up all counts to count rows with data in excel

5. Next, press Enter.

result of the formula

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)

typing formula of SUMPRODUCT and MMULT

2. Then, press Enter.

count rows with data in excel

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.


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

Leave a reply

ExcelDemy
Logo