LEN

[Lebene]

Hi all,

I am using a formula which uses LEN and SUBSTITUTE. The formula works (I didn't learn it by building up, I got it from a tutorial), and I understand what LEN and SUBSTITUTE do separately, however, I don't understand what they are doing in this formula.

I was hoping anyone would explain it in very basic terms? As much as I like that the formula works, I want to also start to understand why the formulas do what they do so that I can begin to become more self-productive in building up formulas as I understand the logic.

Without further ado:
=SUMPRODUCT((LEN($J$2:$J$549)-LEN(SUBSTITUTE($J$2:$J$549, $A2, "")))/LEN($A2))

I use this formula for my lottery syndicate. It counts all the winning numbers from that game (J2:J549) for example, finding the number "01" (A2) in 01-16-23-28-29-(14). I don't understand what LEN and SUBSTITUTE are doing here to accurately sum up and display the frequency that these numbers show up. Especially the minus (-) and divide (/) segments.

 

[Scott T]

If you have

Book1
	A	B	C	D	E	F	G	H	I	J
2	23									1
3										16
4						1				23
5										28
6										29
7										10
Sheet2

Cell Formulas
Range	Formula
F4	F4	=SUMPRODUCT((LEN($J$2:$J$7)-LEN(SUBSTITUTE($J$2:$J$7, $A2, "")))/LEN($A2))

since you are finding the length of a range you get multiple answers. the first LEN gives you

=SUMPRODUCT(({1;2;2;2;2;2}-LEN(SUBSTITUTE($J$2:$J$7, $A2, "")))/LEN($A2))

the substitute gives you

=SUMPRODUCT(({1;2;2;2;2;2}-LEN({"1";"16";"";"28";"29";"10"}))/LEN($A2))

since 28 was removed by substitute it now has a length of 0

=SUMPRODUCT(({1;2;2;2;2;2}-{1;2;0;2;2;2})/LEN($A2))

when yo subtract them you get

=SUMPRODUCT(({0;0;2;0;0;0})/LEN($A2))

the length of A2 is 2

=SUMPRODUCT(({0;0;2;0;0;0})/2)

the division give you. by dividing the 2 is turned into a 1 so if there were multiple 23's then you would get multiple 1's in the array

=SUMPRODUCT({0;0;1;0;0;0})

when there is only one array in sumpoduct as is the case here it is added to give you
1

If you had more then once instance of 23 (say 2)you would get something like this that would sum to 2

=SUMPRODUCT({0;0;1;1;0;0})

 

[Lebene]

If you have

Book1
	A	B	C	D	E	F	G	H	I	J
2	23									1
3										16
4						1				23
5										28
6										29
7										10
Sheet2

Cell Formulas
Range	Formula
F4	F4	=SUMPRODUCT((LEN($J$2:$J$7)-LEN(SUBSTITUTE($J$2:$J$7, $A2, "")))/LEN($A2))

since you are finding the length of a range you get multiple answers. the first LEN gives you

=SUMPRODUCT(({1;2;2;2;2;2}-LEN(SUBSTITUTE($J$2:$J$7, $A2, "")))/LEN($A2))

the substitute gives you

=SUMPRODUCT(({1;2;2;2;2;2}-LEN({"1";"16";"";"28";"29";"10"}))/LEN($A2))

since 23 was removed by substitute it now has a length of 0

=SUMPRODUCT(({1;2;2;2;2;2}-{1;2;0;2;2;2})/LEN($A2))

when yo subtract them you get

=SUMPRODUCT(({0;0;2;0;0;0})/LEN($A2))

the length of A2 is 2

=SUMPRODUCT(({0;0;2;0;0;0})/2)

the division give you. by dividing the 2 is turned into a 1 so if there were multiple 23's then you would get multiple 1's in the array

=SUMPRODUCT({0;0;1;0;0;0})

when there is only one array in sumpoduct as is the case here it is added to give you
1

If you had more then once instance of 23 (say 2)you would get something like this that would sum to 2

=SUMPRODUCT({0;0;1;1;0;0})

You are a maverick! Thank you so much, this makes a lot of sense. In my cells I have all the numbers in one cell with dashes, but this still completely 100%