Counting Occurence of String of Text in a Cell

Poldy

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

Paul Newcome

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,", ""), ",", "")))

Poldy

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

Paul Newcome

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

Hollie Green

Is there a maximum number of times a choice will fall within a cell?