Counting Occurence of String of Text in a Cell
Hi,
I need suggestions on how to count strings of text on a cell.
My cell contains: Triple MXL, SMax Pivot Door, SMax Pivot Door
I need to count: SMax Pivot Door and get the result: 2
The function I'm trying is giving me the wrong result:
=COUNTIF([Product  all]@row, CONTAINS("SMax Pivot Door", @cell))
I also tried to use the dropdown menu and count that, but I can't have duplicates there.
Thank you so much :)
Martin
Answers

First we need to add a comma to the end of the string so that each selections ends with the same value.
=[Product  all]@row + ","
Then we count how many commas there are to get the total number of entries (3).
=LEN([Product  all]@row + ",")  LEN(SUBSTITUTE([Product  all]@row + ",", ",", ""))
Now we need to remove the "SMax Pivot Door," string.
=SUBSTITUTE([Product  all]@row + ",", "SMax Pivot Door,", "")
We take this and find out how many entries are remaining (1).
=LEN([Product  all]@row + ",")  LEN(SUBSTITUTE(SUBSTITUTE([Product  all]@row + ",", "SMax Pivot Door,", ""), ",", ""))
Subtracting the entries remaining from the total entries should give us how many "SMax Pivot Door," entries there are.
=(LEN([Product  all]@row + ",")  LEN(SUBSTITUTE([Product  all]@row + ",", ",", "")))  (LEN([Product  all]@row + ",")  LEN(SUBSTITUTE(SUBSTITUTE([Product  all]@row + ",", "SMax Pivot Door,", ""), ",", "")))

Thank you so much for your answer.
But unfortunately, this doesn't really work for me.
The problem is that this was just an example of what can be in the cell. Usually, I have different stings in the cell as well.
Example: One, One, Two ...I need to count One and get result: 2, and Two and get result: 1
Or: One, Two, One, Two, Two ...Same again...I need to count One and get result: 2, and count Two and get result: 3
I feel like I'm trying to achieve something impossible.
Thank you again :)
Martin

So in the final formula you would replace "SMax Pivot Door," with whatever text string you are wanting to count.

Is there a maximum number of times a choice will fall within a cell?
Help Article Resources
Categories
Check out the Formula Handbook template!