Compare row contents w/Sumproduct or Array formula?
 

Columns: A B C D E F H
Row 1- 0 0 1 2 0 5 A
Row 2- 2 0 0 0 0 1 B
Row 3- 0 0 1 1 x 1 B
Row 4- 4 x 0 0 0 0 B
Row 5- 7 0 0 0 2 2 A

I am looking for a formula that returns the number of rows in which columns
A-F contain at least 3 numbers greater than zero and in which column H="A".
In this case, it would return "2" because rows 1 and 5 fit this criteria.
("x" is the only letter that sometimes appears in the cols A-F, and I would
like to treat it as a zero.)

For a single row, I would use a formula like this:

=Sumproduct(--(COUNTIF(A1:G1,"0")=3),--(H1="A"))

But when I try to convert the A1:G1 to account for multiple rows, it instead
counts the entire range A1:G7. I know this can be done by comparing each
column individually, but in my actual spreadsheet I am analyzing 30+ columns.
Is this possible with sumproduct or an array formula?

Thanks.

Compare row contents w/Sumproduct or Array formula?
 

Hi!

Your posted "table" is columns A thru F and H yet your description uses
column G?

Try this array entered:

=SUMPRODUCT(--(MMULT(--(A1:F50),TRANSPOSE(COLUMN(A1:F5)^0))=3),--(H1:H5="A"))

Biff

"uw805" wrote in message
...
Columns: A B C D E F H
Row 1- 0 0 1 2 0 5 A
Row 2- 2 0 0 0 0 1 B
Row 3- 0 0 1 1 x 1 B
Row 4- 4 x 0 0 0 0 B
Row 5- 7 0 0 0 2 2 A

I am looking for a formula that returns the number of rows in which
columns
A-F contain at least 3 numbers greater than zero and in which column
H="A".
In this case, it would return "2" because rows 1 and 5 fit this criteria.
("x" is the only letter that sometimes appears in the cols A-F, and I
would
like to treat it as a zero.)

For a single row, I would use a formula like this:

=Sumproduct(--(COUNTIF(A1:G1,"0")=3),--(H1="A"))

But when I try to convert the A1:G1 to account for multiple rows, it
instead
counts the entire range A1:G7. I know this can be done by comparing each
column individually, but in my actual spreadsheet I am analyzing 30+
columns.
Is this possible with sumproduct or an array formula?

Thanks.

Compare row contents w/Sumproduct or Array formula?
 

Biff,

Thanks for the help. Sorry about the "G" in my post. They were all
supposed to be "F"s.

The formula you gave me works except for one small thing. It seems to count
"x" with the non-zero numbers, and I need to count it as if it were a zero.
How can I tweak the formula to do that? Thanks.

"Biff" wrote:

Hi!

Your posted "table" is columns A thru F and H yet your description uses
column G?

Try this array entered:

=SUMPRODUCT(--(MMULT(--(A1:F50),TRANSPOSE(COLUMN(A1:F5)^0))=3),--(H1:H5="A"))

Biff

"uw805" wrote in message
...
Columns: A B C D E F H
Row 1- 0 0 1 2 0 5 A
Row 2- 2 0 0 0 0 1 B
Row 3- 0 0 1 1 x 1 B
Row 4- 4 x 0 0 0 0 B
Row 5- 7 0 0 0 2 2 A

I am looking for a formula that returns the number of rows in which
columns
A-F contain at least 3 numbers greater than zero and in which column
H="A".
In this case, it would return "2" because rows 1 and 5 fit this criteria.
("x" is the only letter that sometimes appears in the cols A-F, and I
would
like to treat it as a zero.)

For a single row, I would use a formula like this:

=Sumproduct(--(COUNTIF(A1:G1,"0")=3),--(H1="A"))

But when I try to convert the A1:G1 to account for multiple rows, it
instead
counts the entire range A1:G7. I know this can be done by comparing each
column individually, but in my actual spreadsheet I am analyzing 30+
columns.
Is this possible with sumproduct or an array formula?

Thanks.

Compare row contents w/Sumproduct or Array formula?
 

Try this:

=SUMPRODUCT(--(MMULT(--(A1:F50)*(ISNUMBER(A1:F5)),TRANSPOSE(COLUMN(A1:F5 )^0))=3),--(H1:H5="A"))

Biff

"uw805" wrote in message
...
Biff,

Thanks for the help. Sorry about the "G" in my post. They were all
supposed to be "F"s.

The formula you gave me works except for one small thing. It seems to
count
"x" with the non-zero numbers, and I need to count it as if it were a
zero.
How can I tweak the formula to do that? Thanks.

"Biff" wrote:

Hi!

Your posted "table" is columns A thru F and H yet your description uses
column G?

Try this array entered:

=SUMPRODUCT(--(MMULT(--(A1:F50),TRANSPOSE(COLUMN(A1:F5)^0))=3),--(H1:H5="A"))

Biff

"uw805" wrote in message
...
Columns: A B C D E F H
Row 1- 0 0 1 2 0 5 A
Row 2- 2 0 0 0 0 1 B
Row 3- 0 0 1 1 x 1 B
Row 4- 4 x 0 0 0 0 B
Row 5- 7 0 0 0 2 2 A

I am looking for a formula that returns the number of rows in which
columns
A-F contain at least 3 numbers greater than zero and in which column
H="A".
In this case, it would return "2" because rows 1 and 5 fit this
criteria.
("x" is the only letter that sometimes appears in the cols A-F, and I
would
like to treat it as a zero.)

For a single row, I would use a formula like this:

=Sumproduct(--(COUNTIF(A1:G1,"0")=3),--(H1="A"))

But when I try to convert the A1:G1 to account for multiple rows, it
instead
counts the entire range A1:G7. I know this can be done by comparing
each
column individually, but in my actual spreadsheet I am analyzing 30+
columns.
Is this possible with sumproduct or an array formula?

Thanks.

Compare row contents w/Sumproduct or Array formula?
 

I figured it out. Thanks for your help.

"uw805" wrote:

Biff,

Thanks for the help. Sorry about the "G" in my post. They were all
supposed to be "F"s.

The formula you gave me works except for one small thing. It seems to count
"x" with the non-zero numbers, and I need to count it as if it were a zero.
How can I tweak the formula to do that? Thanks.

"Biff" wrote:

Hi!

Your posted "table" is columns A thru F and H yet your description uses
column G?

Try this array entered:

=SUMPRODUCT(--(MMULT(--(A1:F50),TRANSPOSE(COLUMN(A1:F5)^0))=3),--(H1:H5="A"))

Biff

"uw805" wrote in message
...
Columns: A B C D E F H
Row 1- 0 0 1 2 0 5 A
Row 2- 2 0 0 0 0 1 B
Row 3- 0 0 1 1 x 1 B
Row 4- 4 x 0 0 0 0 B
Row 5- 7 0 0 0 2 2 A

I am looking for a formula that returns the number of rows in which
columns
A-F contain at least 3 numbers greater than zero and in which column
H="A".
In this case, it would return "2" because rows 1 and 5 fit this criteria.
("x" is the only letter that sometimes appears in the cols A-F, and I
would
like to treat it as a zero.)

For a single row, I would use a formula like this:

=Sumproduct(--(COUNTIF(A1:G1,"0")=3),--(H1="A"))

But when I try to convert the A1:G1 to account for multiple rows, it
instead
counts the entire range A1:G7. I know this can be done by comparing each
column individually, but in my actual spreadsheet I am analyzing 30+
columns.
Is this possible with sumproduct or an array formula?

Thanks.

Compare row contents w/Sumproduct or Array formula?
 

Actually, you don't need the "--" inside MMULT:

=SUMPRODUCT(--(MMULT((A1:F50)*(ISNUMBER(A1:F5)),TRANSPOSE(COLUM N(A1:F5)^0))=3),--(H1:H5="A"))

Biff

"Biff" wrote in message
...
Try this:

=SUMPRODUCT(--(MMULT(--(A1:F50)*(ISNUMBER(A1:F5)),TRANSPOSE(COLUMN(A1:F5 )^0))=3),--(H1:H5="A"))

Biff

"uw805" wrote in message
...
Biff,

Thanks for the help. Sorry about the "G" in my post. They were all
supposed to be "F"s.

The formula you gave me works except for one small thing. It seems to
count
"x" with the non-zero numbers, and I need to count it as if it were a
zero.
How can I tweak the formula to do that? Thanks.

"Biff" wrote:

Hi!

Your posted "table" is columns A thru F and H yet your description uses
column G?

Try this array entered:

=SUMPRODUCT(--(MMULT(--(A1:F50),TRANSPOSE(COLUMN(A1:F5)^0))=3),--(H1:H5="A"))

Biff

"uw805" wrote in message
...
Columns: A B C D E F H
Row 1- 0 0 1 2 0 5 A
Row 2- 2 0 0 0 0 1 B
Row 3- 0 0 1 1 x 1 B
Row 4- 4 x 0 0 0 0 B
Row 5- 7 0 0 0 2 2 A

I am looking for a formula that returns the number of rows in which
columns
A-F contain at least 3 numbers greater than zero and in which column
H="A".
In this case, it would return "2" because rows 1 and 5 fit this
criteria.
("x" is the only letter that sometimes appears in the cols A-F, and I
would
like to treat it as a zero.)

For a single row, I would use a formula like this:

=Sumproduct(--(COUNTIF(A1:G1,"0")=3),--(H1="A"))

But when I try to convert the A1:G1 to account for multiple rows, it
instead
counts the entire range A1:G7. I know this can be done by comparing
each
column individually, but in my actual spreadsheet I am analyzing 30+
columns.
Is this possible with sumproduct or an array formula?

Thanks.