Counting Multiple Values In A Cell
 

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(A4:D7, "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(A4:D7, "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


--
DiamondDean
------------------------------------------------------------------------
DiamondDean's Profile: http://www.excelforum.com/member.php...o&userid=26474
View this thread: http://www.excelforum.com/showthread...hreadid=397442

=SUMPRODUCT(LEN(A4:D7)-LEN(SUBSTITUTE(A4:D7,"B","")))

yields a case-sensitive count.

For a case-insensitive count, try:

=SUMPRODUCT(LEN(A4:D7)-LEN(SUBSTITUTE(UPPER(A4:D7),"B","")))

DiamondDean wrote:
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(A4:D7, "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(A4:D7, "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.