asked on Excel formula - count how many times a word appears in a list split down by corresponding field
Hi
I need a formula to to count how many times a word or phase appears in a list, Please note the word or phrase can come in any part of the cell (i.e. have other text before or after it).
The results need to be broken down by details in a corresponding field (in this instance Gender). the attached spreadsheet has a some sample data and also the output from that sample which I hope helps. Also embedded below is an image of the data.
![Example of data]()
Can anyone help?
EE-Example.xlsx
I need a formula to to count how many times a word or phase appears in a list, Please note the word or phrase can come in any part of the cell (i.e. have other text before or after it).
The results need to be broken down by details in a corresponding field (in this instance Gender). the attached spreadsheet has a some sample data and also the output from that sample which I hope helps. Also embedded below is an image of the data.
Can anyone help?
EE-Example.xlsx
Microsoft ExcelMicrosoft Office
Last Comment
Hakan Yılmaz
ASKER
Hi Bill,
Thanks for that, normally I would, but there's a lot of different questions to analyse & it would take me ages to break then out. I did however manage to find Sum Countifs which seems to do the job if I put a wild card in before & after the phrase ...
=SUM(COUNTIFS(UK!$F:$F,"*"
Just checking that through to make sure it fully works
Thanks for that, normally I would, but there's a lot of different questions to analyse & it would take me ages to break then out. I did however manage to find Sum Countifs which seems to do the job if I put a wild card in before & after the phrase ...
=SUM(COUNTIFS(UK!$F:$F,"*"
Just checking that through to make sure it fully works
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ah, I wasn't thinking you would already know all the possible "categories" that could show up in the STAT column.
One thing to keep in mind, that only works well if none of the STAT categories are ever substrings of each other. For example, if you had:
People
People Skills
Then you could get some double counting. As it stands not a problem from your example, just something to keep in mind.
»bp
One thing to keep in mind, that only works well if none of the STAT categories are ever substrings of each other. For example, if you had:
People
People Skills
Then you could get some double counting. As it stands not a problem from your example, just something to keep in mind.
»bp
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
You may try something like this. I attached file with minor differences. This takes into account the possibility of substrings.
=SUMPRODUCT(--($A$3:$A$9=E$2);--ISNUMBER(FIND(", "&$D3&",";", "&$B$3:$B$9&",")))
Once you have the data in that format with one STAT per row, then the result you are after becomes a very simple pivot table, something that Excel is great at. Attached is an example of what I mean, hope this helps.
EE-Example.xlsx
»bp