Occasional 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.

8 REPLIES 8
Occasional Contributor

Re: How to take the count of a text value that repeats

Can someone please assist me with the above question?

Super Contributor

Re: How to take the count of a text value that repeats

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

Re: How to take the count of a text value that repeats

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.
Occasional Contributor

Re: How to take the count of a text value that repeats

Thank you for the update harish :-)

Super Contributor

Re: How to take the count of a text value that repeats

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.

 

Screen Shot 2018-10-22 at 10.15.03 AM.png

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

Certified Master Anaplanner

Re: How to take the count of a text value that repeats

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.

 

 

Certified Master Anaplanner

Re: How to take the count of a text value that repeats

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.

 

 

New Contributor

Re: How to take the count of a text value that repeats

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.