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" wrote in message
...
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.

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.