Number of rows with a non blank cell
 

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

Number of rows with a non blank cell
 

Try...

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

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

Hope this helps!

In article
,
vsoler wrote:

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

Number of rows with a non blank cell
 

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
--
Gary''s Student - gsnu2007i


"vsoler" wrote:

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

Number of rows with a non blank cell
 

On May 20, 1:10*pm, Domenic wrote:
Try...

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

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

Hope this helps!

In article
,



*vsoler wrote:
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- Hide quoted text -

- Show quoted text -

Thank you Domenic, and thank you Gary's student