Count when a range of cells contains a certain word or phraseClosed

Question

Hello,

I have a spreadsheet which I am trying to summarise information from.  We receive enquiries from many different properties we manage, and I'm trying to count how many instances of each property appear through all of the enquiries over a period of time. 

In one column, I have the subject headings from the emails we receive.  These include the name of the property which is always the same (but can be 1 or more words), however depending on the origin of the enquiry and the website formatting, the property name can be anywhere in the subject heading i.e. at the start/middle/end. 

I've tried the countif formula however it doesn't seem to be working as I'm trying to count only when the cells contain the property name (either one word or multiple words), rather than a direct match. 

I have the email subject headings in column B, and the list of properties in column E (which can be one or more words).  Any suggestions? 

e.g. 
Column B: 
Enquiry from website A for "Beach House" 
Website B enquiry for "Beach House" from Guest X 
"House" enquiry from Website C 
... 
(where "Beach House" and "House" are names of properties - i.e. 1 or more words) 

Column E: 
Beach House 
House 
... 

Thanks!

TrowaD · Answer

Hi Hab,

This code will work only if you use quotation marks around the properties.
Otherwise House will also be counted in Beach House.
Column F is used to input the amount of occurrences

Here is the code:
Sub CountProperties()
Dim x, y, lRowB, lRowE As Integer
lRowB = Range("B" & Rows.Count).End(xlUp).Row
lRowE = Range("E" & Rows.Count).End(xlUp).Row
y = 1
Do
    y = y + 1
    Range("F" & y).ClearContents
    x = 1
    Do
        x = x + 1
        If InStr(Range("B" & x), Range("E" & y)) > 0 Then
            Range("F" & y) = Range("F" & y) + 1
        End If
    Loop Until x = lRowB
Loop Until y = lRowE

End Sub
Best regards,
Trowa

Count when a range of cells contains a certain word or phrase

1 response

Similar discussions

Subscribe To Our Newsletter!