Count Non-Blank Rows
 

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:A40)+(B1:B40)+(C1:C4),1,0))

It would be nice to use something like the array formula 1=OR(A1:C10) 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

Count Non-Blank Rows
 

Can you insert a help column in D with the sum across
then countif(D1:D1000,"0")

--
Greetings from New Zealand
Bill K
"Lazzaroni" wrote in message
...
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:A40)+(B1:B40)+(C1:C4),1,0))

It would be nice to use something like the array formula 1=OR(A1:C10) 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

Count Non-Blank Rows
 

Hi!

If I understand you, try this:

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

Returns 3

Biff

"Lazzaroni" wrote in message
...
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:A40)+(B1:B40)+(C1:C4),1,0))

It would be nice to use something like the array formula 1=OR(A1:C10) 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

Count Non-Blank Rows
 

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.

Count Non-Blank Rows
 

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" wrote in message
oups.com...
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.

Count Non-Blank Rows
 

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.

Count Non-Blank Rows
 

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" wrote in message
ups.com...
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.

Count Non-Blank Rows
 

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,""))

Count Non-Blank Rows
 

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

"Harlan Grove" wrote in message
oups.com...
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,""))

Count Non-Blank Rows
 

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.

Count Non-Blank Rows
 

That's slick!

Biff

"Harlan Grove" wrote in message
oups.com...
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.

Count Non-Blank Rows
 

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.

Count Non-Blank Rows
 

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.

"Harlan Grove" wrote:

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

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