Counting Multiple Values In A Cell

[DiamondDean]

I am trying to create a formula that will count multiple values in
cell. For example if I have a spreadsheet that look something lik
this:

Cell A4 = B _____Cell B4 = B_____Cell C4 = A, B_____Cell D4 = A
Cell A5 = A _____Cell B5 = A_____Cell C5 = B_______Cell D5 = A,B
Cell A6 = A _____Cell B6 = B_____Cell C6 = A_______Cell D6 = A
Cell A7 = C _____Cell B7 = A_____Cell C7 = A,C_____Cell D7 = C,B

Formula | =COUNTIF(A47, "A") would return a value of 7

Instead I need a formula that would return the value 10 to include th
cells containing the letter "A".

Formula | =COUNTIF(A47, "B") would return a value of 4

Instead I need a formula that would return the value 7 to include th
cells containing the letter "B".

Formula | =COUNTIF(A4:E7, "C") would return a value of 1

Instead I need a formula that would return the value 3 to include th
cells containing the letter "C".

Any ideas on how I can count the number of single or multiple value
within a cell?
Any suggestions are greatly appreciated. THANKS!

Dea

 

[Aladin Akyurek]

=SUMPRODUCT(LEN(A47)-LEN(SUBSTITUTE(A47,"B","")))

yields a case-sensitive count.

For a case-insensitive count, try:

=SUMPRODUCT(LEN(A47)-LEN(SUBSTITUTE(UPPER(A47),"B","")))

I am trying to create a formula that will count multiple values in a
cell. For example if I have a spreadsheet that look something like
this:

Cell A4 = B _____Cell B4 = B_____Cell C4 = A, B_____Cell D4 = A
Cell A5 = A _____Cell B5 = A_____Cell C5 = B_______Cell D5 = A,B
Cell A6 = A _____Cell B6 = B_____Cell C6 = A_______Cell D6 = A
Cell A7 = C _____Cell B7 = A_____Cell C7 = A,C_____Cell D7 = C,B

Formula | =COUNTIF(A47, "A") would return a value of 7

Instead I need a formula that would return the value 10 to include the
cells containing the letter "A".

Formula | =COUNTIF(A47, "B") would return a value of 4

Instead I need a formula that would return the value 7 to include the
cells containing the letter "B".

Formula | =COUNTIF(A4:E7, "C") would return a value of 1

Instead I need a formula that would return the value 3 to include the
cells containing the letter "C".

Any ideas on how I can count the number of single or multiple values
within a cell?
Any suggestions are greatly appreciated. THANKS!

Dean

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.