Count the instances of text in a comma separated entry

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 !

Welcome to the community, @Chris_Wray! :smiley: 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

1 Like

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