"Biff" wrote...
....
As far as N goes.......

I don't know the exact reason it's needed. It's one of those
quirky type things you just run into.

It usually comes into play when you're trying to deal with
3D references.
....

It's not 3D referencing. Calling INDIRECT or OFFSET with array arguments
results in those functions returning something that behaves like an array of
range references. There are very few other functions that can cope with such
beasts, certainly not SUMPRODUCT. N and T functions convert these into
arrays of numbers or strings, which SUMPRODUCT can handle.