Previous Topic Next Topic

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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - 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

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
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