Avatar of Tom Crowfoot
Tom Crowfoot
Flag for United Kingdom of Great Britain and Northern Ireland 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
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Hakan Yılmaz

8/22/2022 - Mon
Bill Prew

I would strongly recommend adjusting the data into a single value of STAT per row, either at the source before it gets in to Excel, or as a first step in the process in Excel (a macro could be created to break it apart).

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
Tom Crowfoot

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,"*"&$A16&"*",UK!$B:$B,B$2))

Just checking that through to make sure it fully works
ASKER CERTIFIED SOLUTION
Rob Henson

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
Sign up - Free for 7 days
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
Bill Prew

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
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
Hakan Yılmaz

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&",")))

Open in new window

EE-Example.xlsx