Counting multiple numbers in one cell
 

I am trying to count various numbers and letters in multiple cells that are
seperated by commas.

Example

a1 = 1,4
b1 = 3,89,3,16
c1 = 3a,4,7

I only want to count the number of unique entries in each cell (meaning a1
would result in 2, b1 would result in 4, and c1 would result in 3, etc.)

Is there a way to do this?

Please help. Thank you.

Hi
if you only want to count the number of entries per cell try
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

--
Regards
Frank Kabel
Frankfurt, Germany

"clubin" schrieb im Newsbeitrag
...
I am trying to count various numbers and letters in multiple cells
that are
seperated by commas.

Example

a1 = 1,4
b1 = 3,89,3,16
c1 = 3a,4,7

I only want to count the number of unique entries in each cell
(meaning a1
would result in 2, b1 would result in 4, and c1 would result in 3,
etc.)

Is there a way to do this?

Please help. Thank you.

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

HTH
Jason
Atlanta, GA

-----Original Message-----
I am trying to count various numbers and letters in
multiple cells that are
seperated by commas.

Example

a1 = 1,4
b1 = 3,89,3,16
c1 = 3a,4,7

I only want to count the number of unique entries in
each cell (meaning a1
would result in 2, b1 would result in 4, and c1 would
result in 3, etc.)

Is there a way to do this?

Please help. Thank you.
.

Should B1 be 3 or 4 (3 is a lot more tricky)?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"clubin" wrote in message
...
I am trying to count various numbers and letters in multiple cells that
are
seperated by commas.

Example

a1 = 1,4
b1 = 3,89,3,16
c1 = 3a,4,7

I only want to count the number of unique entries in each cell (meaning a1
would result in 2, b1 would result in 4, and c1 would result in 3, etc.)

Is there a way to do this?

Please help. Thank you.

Thank you very much for the formula, very helpful. One other question. Is
there a shorter way to do this for multiple cells in a row (e.g. a1:a5)
rather than just adding up the formulas cell by cell (e.g.
=(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)+(LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)+...etc.)

Thank you again for your help.

Chaim Lubin

"Frank Kabel" wrote:

Hi
if you only want to count the number of entries per cell try
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

--
Regards
Frank Kabel
Frankfurt, Germany

"clubin" schrieb im Newsbeitrag
...
I am trying to count various numbers and letters in multiple cells
that are
seperated by commas.

Example

a1 = 1,4
b1 = 3,89,3,16
c1 = 3a,4,7

I only want to count the number of unique entries in each cell
(meaning a1
would result in 2, b1 would result in 4, and c1 would result in 3,
etc.)

Is there a way to do this?

Please help. Thank you.

Hi
try:
=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,",",""))+1)*(A1:A10<"")
)

--
Regards
Frank Kabel
Frankfurt, Germany

"clubin" schrieb im Newsbeitrag
...
Thank you very much for the formula, very helpful. One other
question. Is
there a shorter way to do this for multiple cells in a row (e.g.
a1:a5)
rather than just adding up the formulas cell by cell (e.g.

=(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)+(LEN(A2)-LEN(SUBSTITUTE(A2,",",
""))+1)+...etc.)

Thank you again for your help.

Chaim Lubin

"Frank Kabel" wrote:

Hi
if you only want to count the number of entries per cell try
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

--
Regards
Frank Kabel
Frankfurt, Germany

"clubin" schrieb im Newsbeitrag
...
I am trying to count various numbers and letters in multiple
cells
that are
seperated by commas.

Example

a1 = 1,4
b1 = 3,89,3,16
c1 = 3a,4,7

I only want to count the number of unique entries in each cell
(meaning a1
would result in 2, b1 would result in 4, and c1 would result in
3,
etc.)

Is there a way to do this?

Please help. Thank you.

Thank you, I hope to be as good as you one day.

"Frank Kabel" wrote:

Hi
try:
=SUMPRODUCT((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,",",""))+1)*(A1:A10<"")
)

--
Regards
Frank Kabel
Frankfurt, Germany

"clubin" schrieb im Newsbeitrag
...
Thank you very much for the formula, very helpful. One other
question. Is
there a shorter way to do this for multiple cells in a row (e.g.
a1:a5)
rather than just adding up the formulas cell by cell (e.g.

=(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)+(LEN(A2)-LEN(SUBSTITUTE(A2,",",
""))+1)+...etc.)

Thank you again for your help.

Chaim Lubin

"Frank Kabel" wrote:

Hi
if you only want to count the number of entries per cell try
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

--
Regards
Frank Kabel
Frankfurt, Germany

"clubin" schrieb im Newsbeitrag
...
I am trying to count various numbers and letters in multiple
cells
that are
seperated by commas.

Example

a1 = 1,4
b1 = 3,89,3,16
c1 = 3a,4,7

I only want to count the number of unique entries in each cell
(meaning a1
would result in 2, b1 would result in 4, and c1 would result in
3,
etc.)

Is there a way to do this?

Please help. Thank you.