Decipher Formula

[Paula]

Would someone please decipher this formula for me in
layman's term?

=SUMPRODUCT(LEN(J357)-LEN(SUBSTITUTE
(J357,"Discard","")))/LEN("Discard")

Thank you

 

[Frank Kabel]

Hi
The SUMPRODUCT part:
SUMPRODUCT(LEN(J357)-LEN(SUBSTITUTE(J357,"Discard","")))

calculates for each cell in the range J357 the difference between the
original string
LEN(J357)
and the string which results if you substitute 'Discard' in these
strings with nothing ""
LEN(SUBSTITUTE(J357,"Discard",""))

These results are then summed. So lets consider you have the following
three strings:
J3: This is a normal string
J4: With Discard
J5: Discard and another Discard

The SUMPRODUCT formula would return the following for these three
cells:
J3: LEN("This is a normal string")-LEN("This is a normal string") = 0
J4: LEN("With Discard")-LEN("With ") = 7
J5: LEN("Discard and another Discard")-LEN(" and another ") = 14

Then it sumes the results and in our example you'll get 21
This is then divided by the length of 'discard' = 7 and the final
result would be 3

So in total this formula counts the number of occurences of the string
'Discard' within the specified range