Count rows with a string in any cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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...
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
 
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.
 
OMG, thank you (and Harlan), I guess this wasn't so straightforward after all.
 
You must log in or register to reply here.

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

Similar Threads

CountIf previous cell formula 0
Multiple arguments 1
Count NonBlank Rows with Criteria in another column 5
COUNT rows on external sheet if between date range 3
count no. of commas (,) in a string in a cell 3
Countif formula perhaps 7
Test for string across multiple cells/sheets... further questions 6
VBA to count colored cells 1

Back
Top