Count rows with a string in any cell

A

andy62

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

Biff

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
 
H

Harlan Grove

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.
Click to expand...

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
 
H

Harlan Grove

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.
Click to expand...

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