Count rows with a string in any cell

I'm trying to engineer a function that counts the number of rows which have a
certain string in any cell. The string could appear in any of about nine
different cells within each row. I thought this would be easy using COUNTIF
inside SUMPRODUCT, but I am not quite getting it. TIA.

Hi!

Kind of light on details.........

Assume the range is A1:I5. The string = "try".

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=SUM(--(MMULT(--(A1:I5="try"),TRANSPOSE(COLUMN(A1:I5)^0))>0))

Biff

"andy62" <[email protected]> wrote in message
news:[email protected]...
> I'm trying to engineer a function that counts the number of rows which
> have a
> certain string in any cell. The string could appear in any of about nine
> different cells within each row. I thought this would be easy using
> COUNTIF
> inside SUMPRODUCT, but I am not quite getting it. TIA.

andy62 wrote...
>I'm trying to engineer a function that counts the number of rows which have a
>certain string in any cell. The string could appear in any of about nine
>different cells within each row. I thought this would be easy using COUNTIF
>inside SUMPRODUCT, but I am not quite getting it. TIA.

As long as you're not trying to search all 65536 rows, you could use
something like the following array formula.

=SUM(--(MMULT(-ISNUMBER(SEARCH("*XyZ*",Rng)),TRANSPOSE(COLUMN(Rng)))<0))

This uses only nonvolatile functions. At th

andy62 wrote...
>I'm trying to engineer a function that counts the number of rows which have a
>certain string in any cell. The string could appear in any of about nine
>different cells within each row. I thought this would be easy using COUNTIF
>inside SUMPRODUCT, but I am not quite getting it. TIA.

As long as you're not trying to search all 65536 rows, you could use
something like the following array formula.

=SUM(--(MMULT(-ISNUMBER(SEARCH("*XyZ*",Rng)),TRANSPOSE(COLUMN(Rng)))<0))

This uses only nonvolatile functions. At the cost of some performance,
you could use OFFSET to shorten the the array formula.

=SUM(--(COUNTIF(OFFSET(Rng,ROW(Rng)-MIN(ROW(Rng)),0,1,),"*XyZ*")>0))

That's not much shorter, so I'd prefer the longer, nonvolatile formula
myself.

OMG, thank you (and Harlan), I guess this wasn't so straightforward after all.

"Biff" wrote:

> Hi!
>
> Kind of light on details.........
>
> Assume the range is A1:I5. The string = "try".
>
> Entered as an array using the key combination of CTRL,SHIFT,ENTER:
>
> =SUM(--(MMULT(--(A1:I5="try"),TRANSPOSE(COLUMN(A1:I5)^0))>0))
>
> Biff
>
> "andy62" <[email protected]> wrote in message
> news:[email protected]...
> > I'm trying to engineer a function that counts the number of rows which
> > have a
> > certain string in any cell. The string could appear in any of about nine
> > different cells within each row. I thought this would be easy using
> > COUNTIF
> > inside SUMPRODUCT, but I am not quite getting it. TIA.
>
>
>

You're welcome!

Biff

"andy62" <[email protected]> wrote in message
news:[email protected]...
> OMG, thank you (and Harlan), I guess this wasn't so straightforward after
> all.
>
> "Biff" wrote:
>
>> Hi!
>>
>> Kind of light on details.........
>>
>> Assume the range is A1:I5. The string = "try".
>>
>> Entered as an array using the key combination of CTRL,SHIFT,ENTER:
>>
>> =SUM(--(MMULT(--(A1:I5="try"),TRANSPOSE(COLUMN(A1:I5)^0))>0))
>>
>> Biff
>>
>> "andy62" <[email protected]> wrote in message
>> news:[email protected]...
>> > I'm trying to engineer a function that counts the number of rows which
>> > have a
>> > certain string in any cell. The string could appear in any of about
>> > nine
>> > different cells within each row. I thought this would be easy using
>> > COUNTIF
>> > inside SUMPRODUCT, but I am not quite getting it. TIA.
>>
>>
>>