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 !
Count the instances of text in a comma separated entry
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 )
Justin_Barrett wrote:
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
The only thing that might be of note is that ‘male’ and ‘female’ aren’t genders, they are ‘sexes’
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.