Count occurrences of text in a range

I have a range of cells that contain various text codes. I need to develop a formula that will count each of those individual codes.

For example, range A1:E1

A1 - c,ce,v
B1 - v,c,ve
C1 - VE,CE,V
D1 - CE,v,C
E1 - V,CE,C
*There is no case sensitivity.

I am attempting to pull out specific codes such as; how many times is C in the range? The complication I keep running into is trying to account for the commas:

=COUNTIF($A$1:$E$1,"*C*")

is that all in 1 cell?

You can try this and enter it as an array Ctrl+Shift+Enter because substitute is case sensitive you enter both cases. I'm sure there are 5 other ways to do this but this works.

Please Login or Register  to view this content.

If you want to count "c" and this is the cell entry:

c,ce,c

What result do you expect?

braydon16,

Attached is an example workbook based on the criteria you described and includes Tony Valko's example.
The sample data is in row 1 from A:F
The code that you want counted is typed into cell B3
The number of times that code appears is calculated by this formula in cell B4:

Please Login or Register  to view this content.

Does that work for you?

Try this...

=SUMPRODUCT(LEN(","&A1:E1&",")-LEN(SUBSTITUTE(","&LOWER(A1:E1)&",",LOWER(",c,"),"")))/LEN(",c,")

EDIT:
Note that the SUBSTITUTE function is case sensitive which is why we need to use the LOWER(...) function in the formula.

You're welcome. We appreciate the feedback!

This also seems to work and is much shorter:

=SUM(COUNTIF(A1:E1,{"c,*","*,c,*","*,c"}))

Originally Posted by Tony Valko

This also seems to work and is much shorter:

=SUM(COUNTIF(A1:E1,{"c,*","*,c,*","*,c"}))

Just to clarify...

That will only count each possible charater configuration once per cell.

For example, if the cell contained:

c,CE,c,c,CV,c

The formula would return 3 counting:

c,CE,c,c,CV,c