Compare row contents w/Sumproduct or Array formula?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Hi!

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

Try this array entered:

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

Biff
 
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.
 
Try this:

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

Biff
 
I figured it out. Thanks for your help.

uw805 said:
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.
Click to expand...
 
Actually, you don't need the "--" inside MMULT:

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

Biff
 
Back
Top