Count non blank rows in a certain range/column Excel

Question

I want to count empty (or non-blank) rows in a given column (or range). Example: I have a column which is spaning over 4 cells width, and each cell has either a single ''x'' or is empty. There is up to 100 rows under this column. Here's a picture to clarify:

Solution 1

You can use array formula. For example, to count the first 10 rows starting from row 2.

=SUM((COUNTBLANK(OFFSET(B2,ROW(1:10)-1,0,1,4))=4)*1)

To count the first 100 rows:

=SUM((COUNTBLANK(OFFSET(B2,ROW(1:100)-1,0,1,4))=4)*1)

Solution 2

Use a new column to get the number of blank cells in each row, then count the number of row in this column which are equal to 4.

Or, more simply, write =QUOTIENT(COUNTBLANK(B2:E2);4) in F2, pull the cell down, then write =SUM(F2:F101) in G2.

If there is exactly 4 blank cell in a row, the F cell will have a value of 1 and the sum will just add all of these 1 to get the number of empty rows.

Solution 3

The COUNTA() function will do that for you. For example:

=COUNTA(A1:A100)

Will return the number of non-blank cells in the range A1:A100