Count If Question, is it possible...

[JayB]

Hey Everyone ,

I have a column with one or two letter codes. Sometimes, this column has multiple codes separated by a "/". For example: AB, A, CA/AB, AB/DD/AD etc. There are a finite number of different codes, about 20. I want to be able to add up the number of times each code is present on that column, regardless of whether it's all alone or with other codes included. Can someone help me?

Example:

Code:
AB
DD/AB
AC
DD
DD/AC/AB
AB/AC
DD
etc...

I would like to count each code:

AB - 4
AC - 3
DD - 4
etc

 

[JayB]

Thanks, that helps quite a bit. My other problem with this is I'm needing to count the number of times a two letter code is present. I was thinking that I could count the "/" and add one. Example: AB/CC/DI would count two "/" and add one. Then when there was just a two letter code, count that as one. I don't know, that seems like a really complicated way to go about it, just a thought I had. Any help with this would be great.

 

[T. Valko]

Thanks, that helps quite a bit. My other problem with this is I'm needing to count the number of times a two letter code is present. I was thinking that I could count the "/" and add one. Example: AB/CC/DI would count two "/" and add one. Then when there was just a two letter code, count that as one. I don't know, that seems like a really complicated way to go about it, just a thought I had. Any help with this would be great.

Using a helper column...

Assuming your data starts in cell A2.

Enter this formula in B2 and copy down as needed:

=LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))+1

Then, you can get the sum total of column B.

=SUM(B:B)

Assumes no empty cells in column A and that none of the entries will begin or terminate with the /. Like this:

/AB/AC/

 

[T. Valko]

If you want a single formula...

Based on the same assumptions as noted in my other reply...

Array entered**:

=SUM(IF(ISNUMBER(FIND("/",A2:A8)),LEN(A2:A8)-LEN(SUBSTITUTE(A2:A8,"/",""))+1,1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[JayB]

So I tried the above formula (T. Valko's), and I use ctrl+shift+enter, but my result is the word TRUE. What am I doing wrong?

 

[T. Valko]

So I tried the above formula (T. Valko's), and I use ctrl+shift+enter, but my result is the word TRUE. What am I doing wrong?

Here's a small sample file that demonstrates this.

Count_delimited_entries.xls 16kb

http://cjoint.com/?1dwrzrdvcxZ