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

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

arawlings — Wed, 13 Mar 2024 13:30:41 GMT

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!

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

txnelson — Wed, 13 Mar 2024 13:35:51 GMT

Here is the formula

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

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

arawlings — Wed, 13 Mar 2024 14:03:34 GMT

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.

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

Jackie_ — Wed, 13 Mar 2024 13:49:48 GMT

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

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

pmroz — Wed, 13 Mar 2024 14:16:52 GMT

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;

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

mmarchandTSI — Wed, 13 Mar 2024 20:09:36 GMT

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