Decipher Formula
 

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

=SUMPRODUCT(LEN(J3:P57)-LEN(SUBSTITUTE
(J3:P57,"Discard","")))/LEN("Discard")

Thank you

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

calculates for each cell in the range J3:P57 the difference between the
original string
LEN(J3:P57)
and the string which results if you substitute 'Discard' in these
strings with nothing ""
LEN(SUBSTITUTE(J3:P57,"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

--
Regards
Frank Kabel
Frankfurt, Germany

"Paula" schrieb im Newsbeitrag
...
Would someone please decipher this formula for me in
layman's term?

=SUMPRODUCT(LEN(J3:P57)-LEN(SUBSTITUTE
(J3:P57,"Discard","")))/LEN("Discard")

Thank you