
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jun 30, 2023 07:32 AM
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:
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jun 30, 2023 01:06 PM
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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jun 30, 2023 01:06 PM
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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 29, 2024 03:04 AM
Thx Alexey!
Sorry for the late reply, did what you suggested and helped!
Thank You!
