# Count lots of values in a list, within a string.

#### RockandGrohl

##### Well-known Member
Hi all,

Been looking at this excellent little formula by Rick:

Count number of occurrences of list of characters in a single string

This is pretty much what I want to do, but I have over 73 values in a list and I want to check them against another list, which contains a comma-separated string of values.

Column J is the list of strings where the values can look like this "Hull, York, Harrogate"

I need to check how many times my range of values appears in each string. So if my range of values is:

Hull
Doncaster
Halifax
York
Newcastle

Then the expected result is "2"

Thanks all.

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

#### Peter_SSs

##### MrExcel MVP, Moderator
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

See if this is it

20 09 08.xlsm
JKLMN
1
2Hull, York, Harrogate2Hull
3Hull, Doncaster, Halifax, York, Newcastle5Doncaster
40Halifax
5London0York
6York1Newcastle
7
Count Strings
Cell Formulas
RangeFormula
K2:K6K2=COUNT(SEARCH(" "&N\$2:N\$6&","," "&J2&","))

#### Peter_SSs

##### MrExcel MVP, Moderator
Actually, that could give false results, try this slight modification

20 09 08.xlsm
JKLMN
1
2Hull, York, Harrogate2Hull
3Hull, Doncaster, Halifax, York, Newcastle5Doncaster
40Halifax
5London0York
6York1Newcastle
7New York0
8
Count Strings
Cell Formulas
RangeFormula
K2:K7K2=COUNT(SEARCH(", "&N\$2:N\$6&",",", "&J2&","))

#### RockandGrohl

##### Well-known Member
Hi, thanks I've changed that, hopefully it updates soon (Excel 2010, Windows)

I've got the formula set up like this:

VBA Code:
=COUNT(SEARCH(", "&[Book2]Sheet2!\$A\$2:\$A\$73&",",", "&J3&","))

Where Book 2 Sheet 2 has the list of placenames.

The first string is:

 Blackpool, Flying from Manchester (MAN), (RS) Preston

And Blackpool is in the list of values (A2:A73) - "Blackpool"

Cheers.

#### Peter_SSs

##### MrExcel MVP, Moderator

I've got the formula set up like this:

VBA Code:
=COUNT(SEARCH(", "&[Book2]Sheet2!\$A\$2:\$A\$73&",",", "&J3&","))
Where Book 2 Sheet 2 has the list of placenames.

The first string is:

Blackpool, Flying from Manchester (MAN), (RS) Preston
And Blackpool is in the list of values (A2:A73) - "Blackpool"
So, is that working for you?

#### RockandGrohl

##### Well-known Member
The result of the formula is "0" for Blackpool when it should be a lot more. When I copy the formula down they are all 0's, so something is amiss.

Cheers.

#### RockandGrohl

##### Well-known Member

In fact if I emulate the example given above, word for word, it shows only one count for "Hull, York, Harrogate" even though there are 2 results in the list "Hull" and "York"

Cheers.

#### Peter_SSs

##### MrExcel MVP, Moderator
In fact if I emulate the example given above, word for word, it shows only one count for "Hull, York, Harrogate" even though there are 2 results in the list "Hull" and "York"
Does it behave differently if you go back in to edit that formula then confirm it with Ctrl+Shift+Enter, not just Enter?

If you still get the same behaviour then it would indicate there must be something different about your sheet compared to mine since you can see that mine worked. It could be that the space characters in your data are not normal space characters.
• Can you check that?
• Can you post some small sample data (from both worksheets) with XL2BB then I can be more sure that I am testing with realistic data?

#### RockandGrohl

##### Well-known Member
Does it behave differently if you go back in to edit that formula then confirm it with Ctrl+Shift+Enter, not just Enter?

If you still get the same behaviour then it would indicate there must be something different about your sheet compared to mine since you can see that mine worked. It could be that the space characters in your data are not normal space characters.
• Can you check that?
• Can you post some small sample data (from both worksheets) with XL2BB then I can be more sure that I am testing with realistic data?

Lol, it does work with CTRL SHIFT ENTER and tbh I have no idea why I didn't try it before. Thanks!!

#### Peter_SSs

##### MrExcel MVP, Moderator
Good news. Excel 365 doesn't require the CSE entry any more and so I'm afraid I easily tend to forget it already.

Replies
5
Views
76
Replies
1
Views
59
Replies
4
Views
230
Replies
4
Views
88
Replies
4
Views
69

### Forum statistics

1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back