Count Multiple Instances of Text in a Cell

[dave roth]

Good Afternoon:

I need to be able to count multiple instances of the same piece of text in a
given cell, i.e. "blue, blue, red, green." How I currently have this setup
is:

=countif(I2:I44,"*blue*")
=countif(I2:I44,"*green*")
=countif(I2:I44,"*red*")
on separate rows. In the above example these will return 1 blue, 1 green,
and 1 red; I need it to return 2 blues.

Thanks very much, and a good weekend to all.

Dave Roth

 

[dave roth]

Sorry; Xl 2003.

 

[CLR]

Your formulas work fine in my Excel97

Vaya con Dios,
Chuck, CABGx3

 

[T. Valko]

Try this:

=(LEN(A12)-LEN(SUBSTITUTE(A12,"blue","")))/LEN("blue")

Note that SUBSTITUTE is case sensitive.

This version takes case into account:

=(LEN(A12)-LEN(SUBSTITUTE(UPPER(A12),"BLUE","")))/LEN("blue")

 

[JE McGimpsey]

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(LEN(I2:I44)-LEN(SUBSTITUTE(I2:I44,"blue","")))/LEN("blue")

 

[CLR]

Oops, misread your post......they don't count multiple instances within a
single cell.

My Bad, sorry

Vaya con Dios,
Chuck, CABGx3

 

[JE McGimpsey]

Really?

It shouldn't.

 

[T. Valko]

Ooops!

I see you're testing a range, not a single cell:

=SUMPRODUCT(LEN(I2:I44)-LEN(SUBSTITUTE(I2:I44,"blue","")))/LEN("blue")

=SUMPRODUCT(LEN(I2:I44)-LEN(SUBSTITUTE(UPPER(I2:I44),"BLUE","")))/LEN("blue")

 

[Dave Peterson]

And to avoid having to array enter the formula:
=SUMPRODUCT(LEN(I2:I44)-LEN(SUBSTITUTE(I2:I44,"blue","")))/LEN("blue")

 

[CLR]

Now that's cool JE, really COOL..............a keeper.

Vaya con Dios,
Chuck, CABGx3

 

[dave roth]

Thank you very much; that's exactly what I need.

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(LEN(I2:I44)-LEN(SUBSTITUTE(I2:I44,"blue","")))/LEN("blue")