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

[Closed]
Report
Posts
1
Registration date
Thursday April 4, 2013
Status
Member
Last seen
April 8, 2013
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
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!

1 reply

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
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