nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

arawlings

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!

Accepted answers

txnelson

Here is the formula

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

All comments

txnelson

Here is the formula

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

arawlings

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.

Jackie_

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

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;

mmarchandTSI

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