How to count occurrence of strings from array (row) in a column

**oshikawa1140** · 07-11-2015, 05:53 AM

Hi,

I am working on a spreadsheet that has some strings in columns A2:K2.
In a separate sheet I have in rows A2:A15 some strings (PP1, PP2, PP3...PP15)
In cell L2 I want to be able to put a formula to count how many time any of the strings in A2:A15 appear in A2:K2.

Thank you any help in greatly appreciated.

**Glenn Kennedy** · 07-11-2015, 06:08 AM

In A2 to K2, are the strings like PP1 or xxxPP1yyy? Might be easier if you posted a sheet...

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

**oshikawa1140** · 07-11-2015, 06:44 AM

Sheet1.png Sheet2.png

Thank Glenn for your quick reply here is a screenshot.

**Glenn Kennedy** · 07-11-2015, 11:05 AM

Hi. Probably the best way to do this is to set up a Named Range (CTRL-F3 to view it). I called it "Codes". This list is totally dynamic and can have items added to it or subtracted from it at any time. Then I used SUM and IF together to get your result.

BtW, you will usually get faster responses from members if you attach a spreadsheet, rather than a picture of one.