Counting Occurances (2003)

  • Thread starter Thread starter PeterM
  • Start date Start date
P

PeterM

I have a cell that contains....

"2 red hat, 1 green glove, 2 red glove, 2 yellow hat"

What I need to be able to do is; count the number of items containing "2
red" and return the value 4.

And; count the number of items containing "2 yellow" and return the value 2.

I've tried using COUNTIF, FIND, SEARCH, etc. and can't come up with a way to
do it.

Thank you in advance for your help!
 
Hi. Here is one way. The 2 is the value, and the 5 is the length of "2
Red"

For "2 Yellow", you would use 2/8 (or 1/4)

=(2/5)*(LEN(A1)-LEN(SUBSTITUTE(A1,"2 red","")))

= = = = = = = = = =
HTH :>)
Dana DeLouis
 
Back
Top