Jul 15 2022 03:08 AM - edited Jul 15 2022 03:11 AM
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 1 | Aberdeen |
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:
Aberdeen | 1 |
Aberdeenshire | 0 |
Glasgow | 0 |
Edinburgh | 0 |
Inverness | 0 |
The results I want to see are:
Aberdeen | 3 |
Aberdeenshire | 2 |
Glasgow | 1 |
Edinburgh | 1 |
Inverness | 1 |
Is there a more appropriate formula to use?
Thank you.
Jul 15 2022 03:26 AM
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.