Skip to main content
Solved

Rollup Count

Forum|alt.badge.img+10

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:

  

 

 

Best answer by Alexey_Gusev

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

View original
This topic has been closed for replies.

2 replies

Alexey_Gusev
Forum|alt.badge.img+25

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

    Forum|alt.badge.img+10

    Thx Alexey!

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

    Thank You!

     

     

    Terms & ConditionsAccessibility statement