Get a count of multiple values from a range within a single cell?

**ziggyztz** · 05-04-2017, 03:36 PM

I have a data set and can identify the cells that contain a value from the range, but sometimes a cell will contain multiple values from the range. I'd like to get a count of each occurrence.

My current formula to identify cells is:

=IF(SUMPRODUCT(COUNTIF(A2,"*"&NamedRange&"*"))>0,"Yes","No")

For example, if a cell contains text as follows:
AB01, AB02, AB03

And each of those values is in my named range, my current formula just counts the cell once. I'd like to return the number of instances the cell contains something from the named range. In this case it would return 3.

I saw a lot of example functions with subtitute and len, but I'm doing it wrong. My named range is bigger and has longer names so I'd prefer not type them in or look for them individually.

**Glenn Kennedy** · 05-04-2017, 03:58 PM

=SUMPRODUCT(LEN(A2)-LEN(SUBSTITUTE(A2,Named Range,"")))

might work. But I'm not confident. if not, please post a sample sheet (not a picture of one)

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**JohnTopley** · 05-04-2017, 04:13 PM

Try

=SUMPRODUCT(--(ISNUMBER(SEARCH(NamedRange,A2))))

**Tsjallie** · 05-04-2017, 04:14 PM

With these data
Count.JPG
this formula counts all the codes (as long as they are separated by commas)

Formula:

=SUMPRODUCT(--(LEN(C1:H1)-LEN(SUBSTITUTE(C1:H1,",",""))=0),--(C1:H1<>""))+

SUMPRODUCT(--(LEN(C1:H1)-LEN(SUBSTITUTE(C1:H1,",",""))>0),LEN(C1:H1)-LEN(SUBSTITUTE(C1:H1,",",""))+1)