Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Count the instances of text in a comma separated entry

Topic Labels: Formulas
1489 3
Showing results for 
Search instead for 
Did you mean: 

I’m trying to count the number of female attendees in a training session - I’ve managed to get them into a text string (eg: ,Female,Female, ) but can’t count the number of times the word ‘Female’ appears.
I just can’t work out the formula to give me a number - help !

3 Replies 3

Welcome to the community, @Chris_Wray! :grinning_face_with_big_eyes: The word “Female” has six characters. Use the SUBSTITUTE() function to replace that word with an empty string. Take the length of the original string, subtract the length of the new string, and divide the result by six.

(LEN({Gender String}) - LEN(SUBSTITUTE({Gender String}, "Female", ""))) / 6


@Justin_Barrett you are a superstar. I’ve been hacking at this for ages and your solution is perfect. (Though the elusive COUNTIF would have been so helpful here )

The only thing that might be of note is that ‘male’ and ‘female’ aren’t genders, they are ‘sexes’