To get a count of the number of strings in each cell, you just need to
count the number of commas and add one. This formula will do that:

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

It assumes that your model numbers are in cell A1 - adjust to suit,
then you can copy down.

As for counting how many strings exist of each colour for a range, you
would need a macro to do that.

Hope this helps.

Pete


On Dec 17, 6:11*pm, Ben in CA
wrote:
Hi,

A user has presented a challenge that I'm wondering if it's possible.

The spreadsheet has columns of cells each containing several model numbers,
and the values are separated by commas and spaces in each cell. (eg. 8890,
x3340, mx750)

To make things more confusing, each model is colored differently depending
on the distributor, and there are several different colors in some cells. In
total, about six different colors are used (green, red, blue orange, gray,
purple).

Is there a way I can create a "SUM count" based on how many individual
strings are in a range of cells?

How about how many strings exist of each color for a range?

Am I asking the impossible?

I know there's some options for sorting by color, etc. in Excel 2007, I
don't know if that helps, though.
(http://msdn.microsoft.com/en-us/library/cc952296.aspx)

I have Excel 2007 and older versions if that helps

Thanks to everyone who tries to figure this out.

Ben