Count comma separated items in a string

[NewOrderFac33]

Good morning,

I have just spent hours poring over some data where I could have saved myself a lot of trouble with a simple formula.

I have a range of cells containing labels such as "Live, Pre-Production", "UAT, Pre-Production, Live", "Live, Inactives, UAT, Development" etc.

I needed to count the number of items in each cell, assuming that they were separated by commas. So, the formula would need to return the value 2 for the first of my examples, 3 for the second and 4 for the third.

It needs to use the logic "If the cell is blank, return 0, if the cell is not blank, but does not contain a comma, return 1, otherwise, return (number of commas +1) i.e. if there are two commas in the string, then there must be three items, and so on.

I understand the logic of what I'm trying to do, I'm just stuck on the bit that counts the number of commas found in the string - can anyone help?

As always, all advice gratefully received!

Pete

 

[Inquirer23]

@Inquirer23

Must the output just contain counts or both the activities and their counts?

Both.

 

[Inquirer23]

=COUNTIF($E$13:$E$16,"*"&H13&"*")

1) Identify your unique values in a column
2) In the adjacent cell, enter the formula =COUNTIF($E$13:$E$16,"*"&H13&"*") where E13:E16 is the range you want to search and H13 is the value you want to find. By inserting H13 between two *, you'll be performing a wildcard search.

Good luck!

This is AWESOME! Thanks, Clyde!!!

Although this will be very useful, it requires that values be pre-defined (controlled) and laid down into columns or rows for proper categorization of counts. I was thinking more of how this formula would apply to Pivot Table-like presentation where you would only define certain criteria then it would count the same way.

 

[Aladin Akyurek]

Both.

In that case, hopefully someone into VBA will pick this up for you.