Identify the correct function to count instances of text

Occasional Visitor

I am reviewing a list of organisations that have provided information on the area or areas of Scotland in which they operate. 

 

This data is extracted from a CRM system and the area or areas of Scotland are all listed in one cell. For example: 

 

Organisation 1Aberdeen 
Organisation 2 Aberdeen, Aberdeenshire 
Organisation 3 Aberdeen, Aberdeenshire, Glasgow, Edinburgh, Inverness 

 

I want to count the number of instances of each area and am using the COUNTIF function. However, it seems to count the exact text of a cell and so I only calculate the number of times each area is specified only once and not every time that each area is specified. 

 

So, for the above example, when I create a COUNTIF function for each of the areas, the results I get are: 

 

Aberdeen1
Aberdeenshire0
Glasgow0
Edinburgh0
Inverness0

 

The results I want to see are: 

 

Aberdeen3
Aberdeenshire2
Glasgow1
Edinburgh1
Inverness1

 

Is there a more appropriate formula to use? 

 

Thank you. 

1 Reply

@LaraMurray 

With the data in A1:B3 and the area names in D1:D5, enter the following formula in E1:

=SUM(--ISNUMBER(SEARCH(", "&D1&", ",", "&$B$1:$B$3&", ")))

If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter.

Fill down.

S1573.png