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

Closed
HABBHR01
Posts
1
Registration date
Thursday April 4, 2013
Status
Member
Last seen
April 8, 2013
- Apr 8, 2013 at 12:28 AM
TrowaD
Posts
2884
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2022
- Apr 8, 2013 at 12:02 PM
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

TrowaD
Posts
2884
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 21, 2022
512
Apr 8, 2013 at 12:02 PM
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
0