I am looking for a formula that can count specific words appearing together in a row. I've tried countifs, but it doesn't seem to work. For example, I want the formula to return the number of occurrences that the word "house" appears in the same row as the word in the table.
Data (rows in excel)
I have a house.
We don't have a big house.
The house is big.
We have a house that is big.
The big house is expensive.
Fixed word for formula
house
Table
big: should return 4
expensive: should return 1
have: should return 3
Hi Matt,
If your data is in A1:A5 and you have the words to search for (e.g. big and house) in B1 and B2 then this works
=COUNT(SEARCH(B1,$A$1:$A$5)*SEARCH(B2,$A$1:$A$5))
Modify the cell/table references as needed - see attached workbook.
Note that searching for strings like big will return a found result when it comes across a word like bigger so you may need to adjust what you are searching for by including spaces before or after the word e.g. "big "
Regards
Phil
Hi Phil, thank you for your quick and useful answer. I should have clarified that the text is not in one cell, but each word separately (please see below an example). Therefore the search area isn't only one column, but an area. The search criteria I'm looking for would look for a combination of words in the same row. If helpful, I'm happy to send you an excel sheet with more details.
A1 B1 C1 D1
I Have a house
Hi Matt,
Yes a workbook with actual data is always better so that mistakes aren't made when recreating data from a written description of it.
Also, please be specific about what word combinations are being searched for and provide examples of results you expect. For example will you always be searching for the same 2 words across all rows? Or will there be different combinations of words looked for in different rows?
regards
Phil
Hi Phil,
Please find attached my file. I've tried to make it as clear as possible, also what the formula should return. As mentioned, I'm interested in getting the word combinations on any given row.
Thank you
Matthias
The attachment didn't make it.
Also, which version of Excel are you using?
Hi,rnI've figured out that I can create cells that combine the text into one, and use your formula - thank you. An additional query I have is whether your formula can be used with an IF function. In my attached file, only to consider those that have the number 1 in column I. rnMany thanksMatthias
You could use a small tweak:
=COUNT(SEARCH($J$1,$H$2:$H$7)*SEARCH(M5,$H$2:$H$7)/($I$2:$I$7=1))
Note: may need array entering depending on your version of Excel.
