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

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

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

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.

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.

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.

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.

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

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

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.

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.

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.

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