Number of rows with a non blank cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vsoler - 20 May 2008 11:24 GMT

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

Reply to this Message

Domenic - 20 May 2008 12:10 GMT

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!

In article
<e9ed3e81-c00b-4102-8139-d11d2178d25b@a70g2000hsh.googlegroups.com>,

> High you all,
>
[quoted text clipped - 15 lines]
>
> Thank you

Reply to this Message

vsoler - 20 May 2008 15:26 GMT

> Try...
>
[quoted text clipped - 28 lines]
>
> - Show quoted text -

Thank you Domenic, and thank you Gary's student

Reply to this Message

Gary''s Student - 20 May 2008 12:34 GMT

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

Signature

Gary''s Student - gsnu2007i

> High you all,
>
[quoted text clipped - 15 lines]
>
> Thank you

Reply to this Message