A.N.M. Mohaimen Shanto
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.
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:
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.
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:
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:
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:
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:
Here, array1 and array2 are those arrays that we want to multiply.
The Basic Syntax of Our Formula:
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)
