Count the # of occurrences in a cell based on a list

[dparnell]

Hello,

I have (i) a column in which each cell contains all of the cities in which companies have offices, and (ii) a column of specific cities that I'd like to count, if they occur in each cell.

In less abstract terms, what i am trying to do is find out how many international offices each company has. One column contains all of the cities in which a company has offices. One column has a list of all of the cities that I'd like to count. Please see the following screenshot - https://www.screencast.com/t/xhxsbqAMX

Please help!

David

 

[James006]

Hello,

With an Array Formula :

INDEX($B$1:$B$20,MATCH(TRUE,ISNUMBER(SEARCH($B$1:$B$20, A1)),0))

Hope this will help

 

[Fluff]

How about
=SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$20,B2)))

 

[dparnell]

How about
=SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$20,B2)))

This was right on the money! Thank you so much!

 

[dparnell]

Hello,

With an Array Formula :

INDEX($B$1:$B$20,MATCH(TRUE,ISNUMBER(SEARCH($B$1:$B$20, A1)),0))

Hope this will help

James, thanks for this, but I was not able to get it to work. In Fluff's solution, I was able to understand where the list and criteria were (B and C, respectively). I am sure it is because I am a novice, but I don't understand how the array formula will point to column C as the searchable criteria.

 

[Peter_SSs]

This was right on the money!

From the looks of it your lists are quite long so I think you may need to check that statement carefully.

Firstly, the C2:C2 range in the formula must not contain blanks - see result in D2 below.
I'm assuming though that isn't a problem, but even with that corrected in E2 the result is incorrect.

<b>Count 1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:387px;" /><col style="width:107px;" /><col style="width:56px;" /><col style="width:49px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Accra; Londonderry; Austin, TX; Phoenix, AZ; Hong Kong</td><td style="font-size:10pt; ">London</td><td style="font-size:10pt; text-align:right; ">19</td><td style="font-size:10pt; text-align:right; ">5</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Londonderry</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Accra</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Derry</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Phoenix</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=SUMPRODUCT(--ISNUMBER<span style=' color:008000; '>(SEARCH<span style=' color:#0000ff; '>($C$2:$C$20,B2)</span>)</span>)</td></tr><tr><td >E2</td><td >=SUMPRODUCT(--ISNUMBER<span style=' color:008000; '>(SEARCH<span style=' color:#0000ff; '>($C$2:$C$6,B2)</span>)</span>)</td></tr></table></td></tr></table>



With your long list I think it highly likely you will have issues in there like the London/Londonderry issue above or names like York/New York, San Francisco/South San Francisco, Tu/Sturbridge etc

To overcome that, try either the standard-entry formula in D2 below or the array-entered formula in E2. That should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}

<b>Count 2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:374px;" /><col style="width:167px;" /><col style="width:36px;" /><col style="width:30px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Accra; Londonderry; Austin, TX; Phoenix, AZ; Hong Kong</td><td style="font-size:10pt; ">London</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">3</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Londonderry</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Accra</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Derry</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Phoenix</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=SUMPRODUCT(--ISNUMBER<span style=' color:008000; '>(SEARCH<span style=' color:#0000ff; '>("|"&C2:C6&"|","|"&SUBSTITUTE<span style=' color:#ff0000; '>(SUBSTITUTE<span style=' color:#804000; '>(B2,"; ","|")</span>,",", "|")</span>&"|")</span>)</span>)</td></tr><tr><td >E2</td><td >{=COUNT(SEARCH<span style=' color:008000; '>("|"&C2:C6&"|","|"&SUBSTITUTE<span style=' color:#0000ff; '>(SUBSTITUTE<span style=' color:#ff0000; '>(B2,"; ","|")</span>,",", "|")</span>&"|")</span>)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table>

 

[dparnell]

From the looks of it your lists are quite long so I think you may need to check that statement carefully.

Firstly, the C2:C2 range in the formula must not contain blanks - see result in D2 below.
I'm assuming though that isn't a problem, but even with that corrected in E2 the result is incorrect.

Count 1

	B	C	D	E
Accra; Londonderry; Austin, TX; Phoenix, AZ; Hong Kong	London
	Londonderry
	Accra
	Derry
	Phoenix

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:387px;"><col style="width:107px;"><col style="width:56px;"><col style="width:49px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]19[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$20,B2))) E2 =SUMPRODUCT(--ISNUMBER(SEARCH($C$2:$C$6,B2))) <tbody> </tbody>

<tbody>
</tbody>

With your long list I think it highly likely you will have issues in there like the London/Londonderry issue above or names like York/New York, San Francisco/South San Francisco, Tu/Sturbridge etc

To overcome that, try either the standard-entry formula in D2 below or the array-entered formula in E2. That should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}

Count 2

	B	C	D	E
Accra; Londonderry; Austin, TX; Phoenix, AZ; Hong Kong	London
	Londonderry
	Accra
	Derry
	Phoenix

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:374px;"><col style="width:167px;"><col style="width:36px;"><col style="width:30px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =SUMPRODUCT(--ISNUMBER(SEARCH("|"&C2:C6&"|","|"&SUBSTITUTE(SUBSTITUTE(B2,"; ","|"),",", "|")&"|"))) E2 {=COUNT(SEARCH("|"&C2:C6&"|","|"&SUBSTITUTE(SUBSTITUTE(B2,"; ","|"),",", "|")&"|"))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Peter_SSs - you are exactly right. I went back and ran both - D2 and E2 - and they are significantly more correct. One observation/question: In the spreadsheet, each "occurrence" of a geography is divided by ONLY a semi-colon. I think that the above formulas allow for separation by a comma as well as a semi-colon. This is causing issues with "Manchester", for instance, which can be both a US and international destination. Could you please help me to modify the above formula so that it will NOT all for separation by a comma? I think that may make this even more accurate.

Thanks so much for your help!

David

 

[Peter_SSs]

OK, what I think you are implying is that if Austin (Texas) is included in the column C list then it will show "Austin, TX" not just "Austin" (All the column C values in your screen shot were just city names by themselves so I thought that must have always been the case hence my use of both comma and semicolon). The good news is that this makes the formulas themselves a bit simpler.

On looking back at your screen shot, I note that Abu Dhabi is listed twice in column C. That would cause a miscalculation if Abu Dhabi occurs in column B as it would count each occurrence twice. Perhaps a clean-up of column C might be in order?

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.

Anyway, try these. If further tweaks are required, tell me which of these formula formats you prefer and I will concentrate on that one.
(The highlighting was just for me checking which cities should be counted)

<b>Count3</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:464px;" /><col style="width:140px;" /><col style="width:36px;" /><col style="width:30px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; ">Accra; Londonderry; Austin, TX; Atlanta, GA; Phoenix, AZ; Hong Kong</td><td style="font-size:10pt; ">London</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; text-align:right; ">4</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; "> </td><td style="background-color:#99cc00; font-size:10pt; ">Londonderry</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; "> </td><td style="background-color:#99cc00; font-size:10pt; ">Accra</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Derry</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; "> </td><td style="background-color:#99cc00; font-size:10pt; ">Austin, TX</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Atlanta</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; "> </td><td style="background-color:#99cc00; font-size:10pt; ">Phoenix, AZ</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=SUMPRODUCT(--ISNUMBER<span style=' color:008000; '>(SEARCH<span style=' color:#0000ff; '>("|"&C2:C8&"|","|"&SUBSTITUTE<span style=' color:#ff0000; '>(B2,"; ","|")</span>&"|")</span>)</span>)</td></tr><tr><td >E2</td><td >{=COUNT(SEARCH<span style=' color:008000; '>("|"&C2:C8&"|","|"&SUBSTITUTE<span style=' color:#0000ff; '>(B2,"; ","|")</span>&"|")</span>)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table>

 

[dparnell]

Peter_SSs - thanks for your help and patience. I think that your solution works, but please see the following screenshot to make sure that we are on the same page with regard to the offices list and the filter list - https://www.screencast.com/t/k6yFRPXMHRhK .
The SUMPRODUCT works very well, so let's stick with that.

I had some duplicates in the filter list because, despite cleaning, trimming and removing all macros from the cells, when I ran the "remove duplicates", it still kept the duplicates! I assumed that there must be some weird macro associated with one of them that might show up in the actual offices' column. When I run this version, it looks really accurate. There seem to be a few outliers, but I think that it may have to do with the duplicates in the filter. I've removed them, but I think that they must have had some funky macro that was in the original data.

Unless you have some other suggestions, I think that this is likely as good as we are going to get!

 

[Peter_SSs]

... please see the following screenshot to make sure that we are on the same page with regard to the offices list and the filter list

Well, I think we are on the same page, but you did not confirm or contradict the following and your next screen shot still does not show any American cities in its column C list.

.. I think you are implying is that if Austin (Texas) is included in the column C list then it will show "Austin, TX" not just "Austin"

I had some duplicates in the filter list because, despite cleaning, trimming and removing all macros from the cells, when I ran the "remove duplicates", it still kept the duplicates!

There is a known bug with 'Remove Duplicates' (further simple example here)
Unless your data includes other anomalies (eg leading or trailing spaces around the city names) that bug can be avoided if you sort your column C list before using 'Remove Duplicates'. Is that feasible for you? Alternatively, a macro could be employed to do that job if you prefer.