Understanding a formula
 

I posted a question earlier and I think my final question got lost. I'm
using the following formula to count the number of letters in a cell:

=SUMPRODUCT(LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8; 9},"")))-9*LEN(A22)

You all were kind enough to explain how the functions work but I still dont
understand why you subtract 1 less than the number of arguments in the
substitute formula and then multiple it by the lenght.

Thanks again for all you help.

I will use the notation N to mean the number of characters in the cell and
n(x) to mean the number of charactes of type x that is n(3) wll be the number
of 3s.

The first number will be N-n(0)
The second number will be N-n(1)
The last number will be N-n(0)
If you total all these you get 10N - n(0)-n(1)-n(2)....-N(9)
If you take away 9N from the above you get N - n(0)-n(1)-n(2)....-N(9) which
is the desired number.

Alok Joshi


"Jordan" wrote:

I posted a question earlier and I think my final question got lost. I'm
using the following formula to count the number of letters in a cell:

=SUMPRODUCT(LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8; 9},"")))-9*LEN(A22)

You all were kind enough to explain how the functions work but I still dont
understand why you subtract 1 less than the number of arguments in the
substitute formula and then multiple it by the lenght.

Thanks again for all you help.