Help

Count the instances of text in a comma separated entry

Topic Labels: Formulas
3856 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Chris_Wray
4 - Data Explorer
4 - Data Explorer

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

21%20AM

Chris_Wray
4 - Data Explorer
4 - Data Explorer

@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’