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:

Xenocryst

Answer 1

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

Ferneferneau

Since the column in which I want to look is 4 cells wide, this formula counts all the ''x'' in the range I specified (B2:E9)... This is not what I want. I want to count non blank rows. – Xenocryst

In your case, what is the difference between a non-blank cell and a cell with "x" in? – Ferneferneau

It's the same thing I just wanted to generalise. – Xenocryst

He wants non-blank rows, not cells – Kossuth

Answer 2

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)

Reflate

Answer 3

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.

Psychodiagnosis