Count Non-Blank Rows

[Guest]

Can anyone suggest a more flexible substitute for the following formula? It
effectively counts the number of non-blank (>0) rows in an array. This
formula is limited, however, in that every column requires a separate
statement. I would like to find a function that could handle an array of any
size with a single statement.

1 0 0
1 1 0
0 0 0
0 0 1

3=SUM(IF((A1:A4>0)+(B1:B4>0)+(C1:C4),1,0))

It would be nice to use something like the array formula 1=OR(A1:C1>0) for
every row in the entire array. Unfortunately, according to this document AND
and OR functions cannot be nested within SUM+IF statements:

http://support.microsoft.com/kb/267982/EN-US/

This is the VBA equivalent of what I am trying to do with an Excel formula:

Dim oRow As Range
Dim cNonBlanks As Long

For Each oRow In Range("50:80").Rows
If Application.CountA(oRow) <> 0 Then
cNonBlanks = cNonBlanks + 1
End If
Next oRow

Thanks

 

[Bill Kuunders]

Can you insert a help column in D with the sum across
then countif(D11000,">0")

 

[Biff]

Hi!

If I understand you, try this:

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(A1:C1,ROW(A1:C4)-ROW(A1),,1))>0))

Returns 3

Biff

 

[Harlan Grove]

Lazzaroni wrote...

Can anyone suggest a more flexible substitute for the following formula? It
effectively counts the number of non-blank (>0) rows in an array. This
formula is limited, however, in that every column requires a separate
statement. I would like to find a function that could handle an array of any
size with a single statement.

1 0 0
1 1 0
0 0 0
0 0 1

....

If your range of 1s and 0s were named M, try the following array
formula.

=COUNT(1/(MMULT(M,TRANSPOSE(COLUMN(M)^0))>0))

Note: this uses only nonvolatile functions, so it recalculates only
when M changes.

 

[Biff]

Harlan, off topic.......

=AVERAGE(A1,A11,A12,IF(A13="x",A14,""))

Why does this fail if A13 <> x ?

Average is supposed to ignore text.

Biff

 

[Harlan Grove]

Biff wrote...

. . . off topic.......

=AVERAGE(A1,A11,A12,IF(A13="x",A14,""))

Why does this fail if A13 <> x ?

Average is supposed to ignore text.

....

Who says?

=AVERAGE(1,"",3)

returns #VALUE!. The aggregation functions (COUNT, SUM, AVERAGE, MIN,
MAX, etc) ignore text in ranges and 3D references. They choke on text
constants and derived text scalars.

 

[Biff]

Who says?

From Excel help:

If an array or reference argument contains text, logical values, or empty
cells, those values are ignored; however, cells with the value zero are
included.

I would think that the IF() falls into the "reference argument [that]
contains text" category but apparently not.

Biff

 

[Harlan Grove]

Biff wrote...

Who says?

From Excel help:

If an array or reference argument contains text, logical values, or empty
cells, those values are ignored; however, cells with the value zero are
included.

I would think that the IF() falls into the "reference argument [that]
contains text" category but apparently not.

....

References are *EXCLUSIVELY* ranges an 3D references. The result of
your IF function call when A13 = "x" is A14, a range reference, but
when A13 <> "x" it's "", which isn't a reference of any kind. Try

=ISREF(IF(A13="x",A14,""))

 

[Biff]

References are *EXCLUSIVELY* ranges an 3D references. The result of

your IF function call when A13 = "x" is A14, a range reference, but
when A13 <> "x" it's "", which isn't a reference of any kind.

Ok, got it. Thanks.

This is what I came up with (in reponse to another post in .Misc)

=SUM(A1,A11:A12,IF(A13="x",A14,0))/(3+(A13="x"))

Biff

Biff wrote...

Who says?

From Excel help:

If an array or reference argument contains text, logical values, or empty
cells, those values are ignored; however, cells with the value zero are
included.

I would think that the IF() falls into the "reference argument [that]
contains text" category but apparently not.

...

References are *EXCLUSIVELY* ranges an 3D references. The result of
your IF function call when A13 = "x" is A14, a range reference, but
when A13 <> "x" it's "", which isn't a reference of any kind. Try

=ISREF(IF(A13="x",A14,""))

 

[Harlan Grove]

Biff wrote...
....

This is what I came up with (in reponse to another post in .Misc)

=SUM(A1,A11:A12,IF(A13="x",A14,0))/(3+(A13="x"))

....

If the goal is averaging A1, A11, A12 and A14 only when A13 = "x", then
just modify your original AVERAGE formula a little - change "" into
{""}. That is,

=AVERAGE(A1,A11:A12,IF(A13="x",A14,""))

returns a #VALUE! error when A13 <> "x", but

=AVERAGE(A1,A11:A12,IF(A13="x",A14,{""}))

returns the same result as =AVERAGE(A1,A11:A12). This is due to
automatic number/text conversion semantics. AVERAGE("1","2") returns
1.5, but AVERAGE({"1","2"}) returns #DIV/0! . Excel *ALWAYS* tries to
convert text scalars (and booleans) to numbers in numeric contexts and
numeric scalars (and booleans) to text in text contexts.

 

[Biff]

That's slick!

Biff

Biff wrote...
...
...

If the goal is averaging A1, A11, A12 and A14 only when A13 = "x", then
just modify your original AVERAGE formula a little - change "" into
{""}. That is,

=AVERAGE(A1,A11:A12,IF(A13="x",A14,""))

returns a #VALUE! error when A13 <> "x", but

=AVERAGE(A1,A11:A12,IF(A13="x",A14,{""}))

returns the same result as =AVERAGE(A1,A11:A12). This is due to
automatic number/text conversion semantics. AVERAGE("1","2") returns
1.5, but AVERAGE({"1","2"}) returns #DIV/0! . Excel *ALWAYS* tries to
convert text scalars (and booleans) to numbers in numeric contexts and
numeric scalars (and booleans) to text in text contexts.

 

[Harlan Grove]

Biff wrote...
....

This is what I came up with (in reponse to another post in .Misc)

=SUM(A1,A11:A12,IF(A13="x",A14,0))/(3+(A13="x"))

....

If the goal is averaging A1, A11, A12 and A14 only when A13 = "x", then
just modify your original AVERAGE formula a little - change "" into
{""}. That is,

=AVERAGE(A1,A11:A12,IF(A13="x",A14,""))

returns a #VALUE! error when A13 <> "x", but

=AVERAGE(A1,A11:A12,IF(A13="x",A14,{""}))

returns the same result as =AVERAGE(A1,A11:A12). This is due to
automatic number/text conversion semantics. AVERAGE("1","2") returns
1.5, but AVERAGE({"1","2"}) returns #DIV/0! . Excel *ALWAYS* tries to
convert text scalars (and booleans) to numbers in numeric contexts and
numeric scalars (and booleans) to text in text contexts.

 

[Guest]

Harlan:

It'll take me some time to understand, but your array formula is exactly
what I was looking for. I can instantly apply it to arrays of any size
without having to generate a separate column for a subtotal.

Thanks to everyone for their help.

L.