Count how many times a phrase appears in a cell

Topic Labels: Formulas
869 2
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hi all,
I am trying to count the number of ways this particular phrase appears in a cell

So for the image above, count the amount of times the string “Late Payment” occurs, i.e would return ‘2’.

Thanks team,

2 Replies 2

(len(arrayjoin(x))-len(substitute(arrayjoin(x),‘Y’,’’)))/len(‘Y’), where x - field, Y - phrase

but i think there should be easier way

@Gabriel_Giannetakis This could be done using a rollup field. Its setup is very similar to a lookup field, but you can use an aggregation formula to run a calculation on the rolled-up data. In your case, I suggest limiting the rolled-up records using a condition (one of the options in the rollup field settings) so that you only retrieve records where your target field contains “Late Payment.” From there, the aggregation formula COUNTA(values) will give you a count of the relevant records, indicating how many of them have late payments.