Forum

Notifications
Clear all

Formula to count occurrence of two words being in one row

8 Posts
3 Users
0 Reactions
220 Views
(@matthi500hotmail-com)
Posts: 4
Active Member
Topic starter
 

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

 
Posted : 01/02/2022 2:56 pm
Philip Treacy
(@philipt)
Posts: 1632
Member Admin
 

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

 
Posted : 02/02/2022 1:39 am
(@matthi500hotmail-com)
Posts: 4
Active Member
Topic starter
 

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

 
Posted : 02/02/2022 5:33 am
Philip Treacy
(@philipt)
Posts: 1632
Member Admin
 

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

 
Posted : 02/02/2022 6:47 am
(@matthi500hotmail-com)
Posts: 4
Active Member
Topic starter
 

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

 
Posted : 02/02/2022 7:36 am
(@debaser)
Posts: 838
Member Moderator
 

The attachment didn't make it.

 

Also, which version of Excel are you using?

 
Posted : 02/02/2022 10:00 am
(@matthi500hotmail-com)
Posts: 4
Active Member
Topic starter
 

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

 
Posted : 02/02/2022 10:02 am
(@debaser)
Posts: 838
Member Moderator
 

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.

 
Posted : 03/02/2022 7:00 am
Share: