Summing column w/o VBA. Help.
 

Greetings,

Problem:
I'd like to sum the last 10 rows in the dynamic column B. This is easy
using VBA but I thought I'd challenge myself and do it just by formula.
Have now accepted failure. Can anyone point me in the right direction?


cheers
Phil

Summing column w/o VBA. Help.
 

You could try

=SUM(OFFSET(B1,MAX(IF(ISBLANK($B$1:$B$65535),0,ROW ($B$1:$B$65535)))-10,0,10,
1))

which is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"alvey" wrote in message
...
Greetings,

Problem:
I'd like to sum the last 10 rows in the dynamic column B. This is easy
using VBA but I thought I'd challenge myself and do it just by formula.
Have now accepted failure. Can anyone point me in the right direction?


cheers
Phil

Summing column w/o VBA. Help.
 

On Mon, 19 Dec 2005 11:29:42 -0000, Bob Phillips wrote:

You could try

=SUM(OFFSET(B1,MAX(IF(ISBLANK($B$1:$B$65535),0,ROW ($B$1:$B$65535)))-10,0,10,
1))

which is an array formula, so commit with Ctrl-Shift-Enter

Thanks Bob. That works a treat.
Will go off and read up on array formulas now.


cheers