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:

score 6 · Answer 1 · answered Sep 21 '16 at 14:11

6

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

answered Sep 21 '16 at 14:11

CallumDA

12,025
6
30
52

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. – L. Perreault Sep 21 '16 at 15:13
In your case, what is the difference between a **non-blank** cell and a cell with "x" in? – CallumDA Sep 21 '16 at 15:23
It's the same thing I just wanted to generalise. – L. Perreault Sep 21 '16 at 15:25
6

He wants non-blank rows, not cells – eshwar Sep 21 '16 at 15:42

score 4 · Answer 2 · answered Oct 12 '17 at 08:44

4

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)

answered Oct 12 '17 at 08:44

kelvin 004

413
2
7

score 3 · Answer 3 · answered May 31 '17 at 13:01

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.

Count non blank rows in a certain range/column Excel

3 Answers3

Linked