Sum with Array Formula

[Gary''s Student]

I am trying to get an array formula to sum up a set of terms.

=OFFSET(cll,0,1)+OFFSET(cll,0,2)+OFFSET(cll,0,3) where cll is a Named Range

=SUM(OFFSET(cll,0,{1,2,3})) does not work

 

[Max]

This seems ok, normal Enter:
=SUM(N(OFFSET(cll,0,{1,2,3})))

 

[daddylonglegs]

What's cll, a single column?

Try

=SUM(OFFSET(cll,0,1,,3))

 

[Gary''s Student]

Thanks - works just great - why is N() necessary??

 

[Gary''s Student]

Thank you - very neat.

 

[Max]

.. why is N() necessary??

Perhaps this is best explained by quoting from a past posting by Harlan:

".. OFFSET only returns Range references. OFFSET called with array 1st, 2nd
or 3rd arguments returns something what seems to be an array of range
references. Excel can't deal with such beasts when used as arithmetic
operands or arguments to most functions. Fortunately, N() is one of the
exceptions, and it effectively converts arrays of range references to
arrays of numbers (note: it converts entries that aren't numeric into
numeric zero). The T() function does the same for strings ... "

An interesting obs about using the earlier struct
is that it allows the omission of an intervening col in the sum, eg:
=SUM(N(OFFSET(cll,0,{1,3})))
will omit the 2nd col's values in the sum,

This is probably not possible with using the width param (daddy's
suggestion)

 

[Gary''s Student]

Thank you.
--
Gary''s Student - gsnu200787

Perhaps this is best explained by quoting from a past posting by Harlan:

".. OFFSET only returns Range references. OFFSET called with array 1st, 2nd
or 3rd arguments returns something what seems to be an array of range
references. Excel can't deal with such beasts when used as arithmetic
operands or arguments to most functions. Fortunately, N() is one of the
exceptions, and it effectively converts arrays of range references to
arrays of numbers (note: it converts entries that aren't numeric into
numeric zero). The T() function does the same for strings ... "

An interesting obs about using the earlier struct
is that it allows the omission of an intervening col in the sum, eg:
=SUM(N(OFFSET(cll,0,{1,3})))
will omit the 2nd col's values in the sum,

This is probably not possible with using the width param (daddy's
suggestion)

 

[Max]

Welcome, GS