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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paul - 26 Oct 2004 15:48 GMT

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

Thanks.

Reply to this Message

Frank Kabel - 26 Oct 2004 15:56 GMT

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

Signature

--
Regards
Frank Kabel
Frankfurt, Germany

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

Reply to this Message

David McRitchie - 26 Oct 2004 16:39 GMT

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,":",""))))

Reply to this Message

Gord Dibben - 26 Oct 2004 21:50 GMT

Paul

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

Gord Dibben Excel MVP

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

Reply to this Message