Compare row contents w/Sumproduct or Array formula?

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

[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

"uw805" <uw805@discussions.microsoft.com> wrote in message
news:D151975A-77A1-4101-9DCF-543F6FDB220D@microsoft.com...
> 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.
>

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

"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:F5>0),TRANSPOSE(COLUMN(A1:F5)^0))>=3),--(H1:H5="A"))
>
> Biff
>
> "uw805" <uw805@discussions.microsoft.com> wrote in message
> news:D151975A-77A1-4101-9DCF-543F6FDB220D@microsoft.com...
> > 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.
> >
>
>
>

[Biff]

Try this:

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

Biff

"uw805" <uw805@discussions.microsoft.com> wrote in message
news:028BD073-0DD1-4D63-B46B-F301842C50F4@microsoft.com...
> 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:F5>0),TRANSPOSE(COLUMN(A1:F5)^0))>=3),--(H1:H5="A"))
>>
>> Biff
>>
>> "uw805" <uw805@discussions.microsoft.com> wrote in message
>> news:D151975A-77A1-4101-9DCF-543F6FDB220D@microsoft.com...
>> > 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.
>> >
>>
>>
>>

[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

"Biff" <biffinpitt@comcast.net> wrote in message
news:urFAV6SiGHA.836@TK2MSFTNGP02.phx.gbl...
> Try this:
>
> =SUMPRODUCT(--(MMULT(--(A1:F5>0)*(ISNUMBER(A1:F5)),TRANSPOSE(COLUMN(A1:F5)^0))>=3),--(H1:H5="A"))
>
> Biff
>
> "uw805" <uw805@discussions.microsoft.com> wrote in message
> news:028BD073-0DD1-4D63-B46B-F301842C50F4@microsoft.com...
>> 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:F5>0),TRANSPOSE(COLUMN(A1:F5)^0))>=3),--(H1:H5="A"))
>>>
>>> Biff
>>>
>>> "uw805" <uw805@discussions.microsoft.com> wrote in message
>>> news:D151975A-77A1-4101-9DCF-543F6FDB220D@microsoft.com...
>>> > 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.
>>> >
>>>
>>>
>>>
>
>

[uw805]

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:F5>0),TRANSPOSE(COLUMN(A1:F5)^0))>=3),--(H1:H5="A"))
> >
> > Biff
> >
> > "uw805" <uw805@discussions.microsoft.com> wrote in message
> > news:D151975A-77A1-4101-9DCF-543F6FDB220D@microsoft.com...
> > > 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.
> > >
> >
> >
> >