Need Formula

[CARBOB]

I need a formula in Column G that will count the individual digits in Columns B:F in this order
1,2,3,4,5,6,7,8,9,0.

Example 6 8 15 23 25 1210210100
1234567890

Excel Workbook
	A	B	C	D	E	F	G
7	2/25/2011	6	8	15	23	25	1210210100
8	2/24/2011	1	5	13	33	35	2040200000
9	2/23/2011	12	27	30	32	35	1330101001
Sheet1

Excel 2007

 

[MisterBate5]

This will return the number of ones

=LEN(B7&C7&D7&E7&F7)-LEN(SUBSTITUTE(B7&C7&D7&E7&F7,"1",""))

It would be a long formula but you could wrap that in a =concatenate() and each argument would be the formula above with the relevant digit in place of "1" in the substitute()

 

[CARBOB]

It works and thank you. Maybe someone will come up with a shorter version.

Excel Workbook
	B	C	D	E	F	G
7	6	8	15	23	25	1210210100
8	1	5	13	33	35	2040200000
Sheet1

Excel 2007

Cell Formulas
Range	Formula
G7	=CONCATENATE(LEN(B7&C7&D7&E7&F7)-LEN(SUBSTITUTE(B7&C7&D7&E7&F7,"1",""))&LEN(B7&C7&D7&E7&F7)-LEN(SUBSTITUTE(B7&C7&D7&E7&F7,"2",""))&LEN(B7&C7&D7&E7&F7)-LEN(SUBSTITUTE(B7&C7&D7&E7&F7,"3",""))&LEN(B7&C7&D7&E7&F7)-LEN(SUBSTITUTE(B7&C7&D7&E7&F7,"4",""))&LEN(B7&C7&D7&E7&F7)-LEN(SUBSTITUTE(B7&C7&D7&E7&F7,"5",""))&LEN(B7&C7&D7&E7&F7)-LEN(SUBSTITUTE(B7&C7&D7&E7&F7,"6",""))&LEN(B7&C7&D7&E7&F7)-LEN(SUBSTITUTE(B7&C7&D7&E7&F7,"7",""))&LEN(B7&C7&D7&E7&F7)-LEN(SUBSTITUTE(B7&C7&D7&E7&F7,"8",""))&LEN(B7&C7&D7&E7&F7)-LEN(SUBSTITUTE(B7&C7&D7&E7&F7,"9",""))&LEN(B7&C7&D7&E7&F7)-LEN(SUBSTITUTE(B7&C7&D7&E7&F7,"0","")))
G8	=CONCATENATE(LEN(B8&C8&D8&E8&F8)-LEN(SUBSTITUTE(B8&C8&D8&E8&F8,"1",""))&LEN(B8&C8&D8&E8&F8)-LEN(SUBSTITUTE(B8&C8&D8&E8&F8,"2",""))&LEN(B8&C8&D8&E8&F8)-LEN(SUBSTITUTE(B8&C8&D8&E8&F8,"3",""))&LEN(B8&C8&D8&E8&F8)-LEN(SUBSTITUTE(B8&C8&D8&E8&F8,"4",""))&LEN(B8&C8&D8&E8&F8)-LEN(SUBSTITUTE(B8&C8&D8&E8&F8,"5",""))&LEN(B8&C8&D8&E8&F8)-LEN(SUBSTITUTE(B8&C8&D8&E8&F8,"6",""))&LEN(B8&C8&D8&E8&F8)-LEN(SUBSTITUTE(B8&C8&D8&E8&F8,"7",""))&LEN(B8&C8&D8&E8&F8)-LEN(SUBSTITUTE(B8&C8&D8&E8&F8,"8",""))&LEN(B8&C8&D8&E8&F8)-LEN(SUBSTITUTE(B8&C8&D8&E8&F8,"9",""))&LEN(B8&C8&D8&E8&F8)-LEN(SUBSTITUTE(B8&C8&D8&E8&F8,"0","")))

 

[Domenic]

Try...

G7, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(MMULT(LEN(B7:F7)-LEN(SUBSTITUTE(B7:F7,{1;2;3;4;5;6;7;8;9;0},"")),TRANSPOSE(COLUMN(B7:F7)^0))*10^{9;8;7;6;5;4;3;2;1;0})

 

[CARBOB]

Try...

G7, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(MMULT(LEN(B7:F7)-LEN(SUBSTITUTE(B7:F7,{1;2;3;4;5;6;7;8;9;0},"")),TRANSPOSE(COLUMN(B7:F7)^0))*10^{9;8;7;6;5;4;3;2;1;0})

Much neater!!! Thanks Domenic.