Rollup Count

Miroslav_Tunjic · ‎Jun 30, 2023

Hi

this should be simple but i am stuck

A
A
A
B
B

Whats the formula for a rollup in a linked table to have "3A, 2B" as a result?

thx!

:M:

Alexey_Gusev · ‎Jun 30, 2023

Hi,
I think it's smth like LEN(Field)-LEN(SUBSTITUTE(Field,'A','')) & 'A'
same for B, C etc
but instead of Field should be ARRAYJOIN(Field) or CONCATENATE(Field)
or (values) instead of (Field)
and if your A, B are words longer than 1 char, you should divide on it's LEN
if you have many letters, it's wise to put them all in a vertical list and use a formula to create a formula.

Note: hardcoding values in formula is a bad idea sometimes. If you create new linked field to a new table and copy this column, and add Count field in new table, you get pivot table close to your desired result

See Solution in Thread

Alexey_Gusev · ‎Jun 30, 2023

Hi,
I think it's smth like LEN(Field)-LEN(SUBSTITUTE(Field,'A','')) & 'A'
same for B, C etc
but instead of Field should be ARRAYJOIN(Field) or CONCATENATE(Field)
or (values) instead of (Field)
and if your A, B are words longer than 1 char, you should divide on it's LEN
if you have many letters, it's wise to put them all in a vertical list and use a formula to create a formula.

Note: hardcoding values in formula is a bad idea sometimes. If you create new linked field to a new table and copy this column, and add Count field in new table, you get pivot table close to your desired result

Miroslav_Tunjic · ‎Jan 29, 2024

Thx Alexey!

Sorry for the late reply, did what you suggested and helped!

Thank You!