First you need to reorganize /clean your data so that you have 1 site name per cell. This is what you have now:
You want to have this:
There should be one site name per cell and it should correspond to Name and Nick name.
Then count([Site name]) will work as you expect it.
I hope this helps.
I am assuming few things before proceeding.
You have multiple sites and values are added in single column separated by fixed pattern.
e.g. in you case 1.Site1 2.Site2 3.Site3 or may be by commas SIte1,Site2,Site3
Number of Sites
Len(REGEXP_REPLACE(Replace([Site name],' ',""),'[aA1-zZ9]',""))
This logic removes all the blanks, small letter, capital letter and numerics from the field and displays whatever is remaning, so in above case that would be ... and LEN function calculates the length which would turn out to be same as number of sites.
In order to work with this solution you have basically get rid of all other characters from the string to just have unique identifier either (. or , or anything else)
Please find the attached workbook for your reference.
Note: I personally believe the larger the data volume such string functions could get very heavy, ideally you should get your data cleaned as suggested earlier.
Count of Sites.twbx 10.0 KB
Thanks for the suggestion.
In my case I am using data source as postgre sql and data is quite large. So,in that case what should be my approach to find the count ?
you can probably try to get multi valued column into separate rows. if you need to look into Site details. If you just need count of sites for current analysis without being bother about actual sites, then you can probably write a sql to calculate numbers of separators using similar logic in the back end
I may not be able to give your further insights into postgre sql due to my lack of exposure.