Number of rows with a non blank cell

[vsoler]

High you all,

I need a single formula that gives me, for a rectangular area, the
number of rows that have at least one non blank cell.

Say, for example, that in A1:M20 all the cells are blank except

B10 contains "X"
F10 contains 9
C15 contains "X"

The result is 2, because only 2 rows, numbers 10 and 15, contain some
data

I have been able the get the correct figure using intermediate
results. What I need now is a single formula that gives me the final
result directly.

Thank you

 

[Domenic]

Try...

=SUM(IF(MMULT((A1:M20<>"")+0,TRANSPOSE(COLUMN(A1:M20)^0))>0,1))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Gary''s Student]

Try this UDF:

Function cntRows(r As Range) As Long

cntRows = 0
lr = r.Rows.Count + r.Row - 1
lc = r.Columns.Count + r.Column - 1
fr = r.Row
fc = r.Column

For i = fr To lr
Set rr = Range(Cells(i, fc), Cells(i, lc))
If Application.WorksheetFunction.CountA(rr) > 0 Then
cntRows = cntRows + 1
End If
Next

End Function

 

[vsoler]

Try...

=SUM(IF(MMULT((A1:M20<>"")+0,TRANSPOSE(COLUMN(A1:M20)^0))>0,1))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!












- Show quoted text -

Thank you Domenic, and thank you Gary's student