Compare row contents w/Sumproduct or Array formula?

U

uw805

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.
 
B

Biff

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
 
U

uw805

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.
 
B

Biff

Try this:

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

Biff
 
U

uw805

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.
 
B

Biff

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top