sum up number of times a colon ":" in a row

[paul]

Could anyone tell how to sum up the number of times of a colon ":" appeared
in a row.

Thanks.

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((LEN(A1:X1)-LEN(SUBSTITUTE(A1:X1,":",""))))

 

[David McRitchie]

You can drop the leftmost and rightmost parens of Frank's reply
=SUMPRODUCT(LEN(A1:X1)-LEN(SUBSTITUTE(A1:X1,":","")))
instead of
=SUMPRODUCT((LEN(A1:X1)-LEN(SUBSTITUTE(A1:X1,":",""))))

 

[Gord Dibben]

Paul

=COUNTIF(1:1,CHAR(58))

Gord Dibben Excel MVP