Contributor

## How to take the count of a text value that repeats

I have got a text field (column) say X, it has got values

x1

x2

x2

x3

wanted to creat a new line item to take the count with same value, for eg: x2 count will be 2 since it has got two entries.

1 ACCEPTED SOLUTION

Accepted Solutions
Certified Master Anaplanner

Hi anees,

I have created something simple, which you can use as a reference.

With the modules below, you can enter any text, and the formulas will look for the 'specific text' hidden with a long text.

You can also enter 'specific text' multiple times in the same cell.

I have created 2 modules, i.e. Reference Module A, Random Module A.

Random Module A is the module where you have line items where you want to count the number of occurrences of 'specific text'.

Reference Module A is the module where you input the 'specific text' that you want to count, i.e. x1.

The formula of the different line items are:

Random Module A.Replace = SUBSTITUTE(Line Item A, Reference Module A.Reference Line Item, "")

Random Module A.'Line Item A (Balance)' = LENGTH(Line Item A) - LENGTH(Replace)

Random Module A.Ref Text Count = 'Line Item A (Balance)' / Reference Module A.Length of Ref Line Item

Reference Module A.Length of Ref Line Item = LENGTH(Reference Line Item)

Reference Module A.Count = Random Module A.Ref Text Count

A bit of description for how the different line items work together.

1. Random Module A.Replace looks for the 'specific text' and replace it with "", therefore nullifying the 'specific text'.

2. Random Module A.'Line Item A (Balance)' calculates the number of characters that have been replaced.

3. Random Module A.Ref Text Count calculates the number of times the 'specific text' appears.

Thanks,

LC

10 REPLIES 10
Contributor

Can someone please assist me with the above question?

Super Contributor

Hello

We do not have Count If function but if the said value is a valid list item belonging to a list we do have several workarounds.

Super Contributor
Hello

We do not have Count If function but if the said value is a valid list item belonging to a list we do have several workarounds.
Contributor

Thank you for the update harish 🙂

Certified Master Anaplanner

Hi anees,

I have created something simple, which you can use as a reference.

With the modules below, you can enter any text, and the formulas will look for the 'specific text' hidden with a long text.

You can also enter 'specific text' multiple times in the same cell.

I have created 2 modules, i.e. Reference Module A, Random Module A.

Random Module A is the module where you have line items where you want to count the number of occurrences of 'specific text'.

Reference Module A is the module where you input the 'specific text' that you want to count, i.e. x1.

The formula of the different line items are:

Random Module A.Replace = SUBSTITUTE(Line Item A, Reference Module A.Reference Line Item, "")

Random Module A.'Line Item A (Balance)' = LENGTH(Line Item A) - LENGTH(Replace)

Random Module A.Ref Text Count = 'Line Item A (Balance)' / Reference Module A.Length of Ref Line Item

Reference Module A.Length of Ref Line Item = LENGTH(Reference Line Item)

Reference Module A.Count = Random Module A.Ref Text Count

A bit of description for how the different line items work together.

1. Random Module A.Replace looks for the 'specific text' and replace it with "", therefore nullifying the 'specific text'.

2. Random Module A.'Line Item A (Balance)' calculates the number of characters that have been replaced.

3. Random Module A.Ref Text Count calculates the number of times the 'specific text' appears.

Thanks,

LC

Frequent Contributor

Hello Anees,

one option would be to genereate a list, based on this line item.

So you will have a list that will say x1, x2, x3.

Then you can can create a second lineItem which has a FINDITEM('new list', text field) formula.

Then you need to create a third line items 'count' where you can put as a formula 1. So that it is always shows a 1.

Then you can create a count module. Which will have the 'new list' and you need to pick up the count lineitem and SUM on the second line item. This will give you the number of times the text has been inputted.

This value you then can retrieve in the original module.

Attached are some screenshots of the modules in table and blueprint.

Frequent Contributor

Hello Anees,

one option would be to genereate a list, based on this line item.

So you will have a list that will say x1, x2, x3.

Then you can can create a second lineItem which has a FINDITEM('new list', text field) formula.

Then you need to create a third line items 'count' where you can put as a formula 1. So that it is always shows a 1.

Then you can create a count module. Which will have the 'new list' and you need to pick up the count lineitem and SUM on the second line item. This will give you the number of times the text has been inputted.

This value you then can retrieve in the original module.

Attached are some screenshots of the modules in table and blueprint.

Contributor

The workarounds are good, but in some cases the additional line items/modules will increase the size more than desired. In the case of large data HUBS where these "counts" are needed for data validation purposes, a function similar to excel "count" would be very useful and help reduce model size and complexity.

Certified Master Anaplanner

Hi, Please have the values in column as List i.e. create a list for the values in column (let's say list x)

Then create a line item(count) in module with Number format and value 1 .

count =1

Then create a line item with number format and use below formula:-

Count[SUM: x, LOOKUP: x]

This will capture the count. Hope this helps.