That is quite an impressive formula. I would never have thought of that in a million years.
SUMPRODUCT acts as a way to iterate through ranges and compute the product of corresponding elements in the ranges. For example, if you use a formula like this:
the result is A1*B1 + A2*B2 + A3*B3.
In this case, the range is a series of rows, starting at row 1 and continuing to the row that is identified as the number of characters in the string in A5.
The tricky part here is that we don't really care about rows, but we care about the row numbers of those rows--it's a clever way to force SUMPRODUCT to iterate through every character in A5. For each character, if it is a digit (ISNUMBER), it multiplies that digit times the power of 10 corresponding to its position, then sums all those products up. I haven't quite sorted out how it determines the power of 10 to use, but that is the principle in play here.
Bookmarks