cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
The Discovery Summit 2025 Call for Content is open! Submit an abstract today to present at our premier analytics conference.
Choose Language Hide Translation Bar
arawlings
Level II

How to count the entries in a single cell in each row?

Hello All,

 

Below is the example I am working with. "Column-to-Count" has several entries in each cell within that column. I want to output the count of each cell, which is the "Count" column.

 

|    Column-to-Count    |    Count    |

|    ab1, tgh9o, pl79s    |    3             |

|    ab1, fisc8u               |    2             |

|    ab1, psivb, 7ushg8 |    3             |

 

Is there a formula type that I can use to generate the "Count" column? I looked at the "count" formula type and that did not seem to be what I need.

 

Thank You!

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: How to count the entries in a single cell in each row?

Here is the formula

Length( Words( :"Column-to-Count"n, "," ) )
Jim

View solution in original post

5 REPLIES 5
txnelson
Super User

Re: How to count the entries in a single cell in each row?

Here is the formula

Length( Words( :"Column-to-Count"n, "," ) )
Jim
arawlings
Level II

Re: How to count the entries in a single cell in each row?

Excellent - this works great - thank you!

 

@txnelson follow-up question, what if there are multiple "ab" items and I just want to count those? 

 

|    Column-to-Count                    |     ab Count    |

|    ab1, ab2, ab3, tgh9o, pl79s    |          3             |

|    ab1, fisc8u                               |          1             |

|    ab1, psivb, 7ushg8                 |          1             |

 

I tried to use the formula above with Length( Words( :"Column-to-Count"n, "ab" ) ) and it did not give me the correct count.

mmarchandTSI
Level V

Re: How to count the entries in a single cell in each row?

Here's a good way to do that.  I use Trim() to eliminate leading whitespace and Starts With() to make sure I don't count entries that just happen to contain "ab" somewhere.  Filter Each() requires JMP 16+.

 

Length( Filter Each( {v, i}, Words( :"Column-to-Count"n, "," ), Starts With( Trim( v ), "ab" ) ) )

 

Jackie_
Level VI

Re: How to count the entries in a single cell in each row?

Another way

 

New Table( "Sample table",
	Add Rows( 3 ),
	New Column( "Column-to-count",
		Character,
		Nominal,
		Set Values( {"ab1, tgh9o, pl79s", "ab1, fisc8u", "ab1, psivb, 7ushg8"})
	),
	New Column("Count",
	Numeric,
	Continuous,
	Formula(N Items( Words( :"Column-to-count"n, ", " ) ))
		
	)
)
pmroz
Super User

Re: How to count the entries in a single cell in each row?

To count only elements that have "ab", here's a brute force formula

alist = Words( :"Column-to-count"n, ", " );
acount = 0;
For( i = 1, i <= N Items( alist ), i++,
	If( Contains( alist[i], "ab" ),
		acount
		++)
);
acount;