Sum a column of variable length?

I have to build a workbook where columns of data will be cut and pasted into
Tab 1, then the column totals need to appear automatically on Tab 2. The
problem is that the number of rows of data in Tab 1 will be variable and
unpredictable, although the columns will always start in a known row.

I might be able to do it by something like

=SUM('Tab 1'!I7:I65535)

but is there a more precise way?

Here is one way

=SUM(OFFSET('Tab 1'!I7,,,'Tab 1'!COUNTA(I:I)-'Tab 1'!COUNTA(I1:I6)))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Brian" <bxxcfilm@nildram.co.uk> wrote in message
news:ens8ghdCFHA.3732@TK2MSFTNGP14.phx.gbl...
> I have to build a workbook where columns of data will be cut and pasted
into
> Tab 1, then the column totals need to appear automatically on Tab 2. The
> problem is that the number of rows of data in Tab 1 will be variable and
> unpredictable, although the columns will always start in a known row.
>
> I might be able to do it by something like
>
> =SUM('Tab 1'!I7:I65535)
>
> but is there a more precise way?
>
>
>

Bob,

This generates an error. The "Tab 1" after the 3 commas is highlighted. I am
using XL2000.

Brian


"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:eKAuMqdCFHA.1836@tk2msftngp13.phx.gbl...
> Here is one way
>
> =SUM(OFFSET('Tab 1'!I7,,,'Tab 1'!COUNTA(I:I)-'Tab 1'!COUNTA(I1:I6)))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Brian" <bxxcfilm@nildram.co.uk> wrote in message
> news:ens8ghdCFHA.3732@TK2MSFTNGP14.phx.gbl...
> > I have to build a workbook where columns of data will be cut and pasted
> into
> > Tab 1, then the column totals need to appear automatically on Tab 2. The
> > problem is that the number of rows of data in Tab 1 will be variable and
> > unpredictable, although the columns will always start in a known row.
> >
> > I might be able to do it by something like
> >
> > =SUM('Tab 1'!I7:I65535)
> >
> > but is there a more precise way?
> >
> >
> >
>
>

Brian,

Sorry my mistake, I added the sheets at the end,wrongly. Try this

=SUM(OFFSET('Tab 1'!I7,,,COUNTA('Tab 1'!I:I)-COUNTA('Tab 1'!I1:I6)))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Brian" <bxxcfilm@nildram.co.uk> wrote in message
news:OZbk0neCFHA.2384@TK2MSFTNGP14.phx.gbl...
> Bob,
>
> This generates an error. The "Tab 1" after the 3 commas is highlighted. I
am
> using XL2000.
>
> Brian
>
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:eKAuMqdCFHA.1836@tk2msftngp13.phx.gbl...
> > Here is one way
> >
> > =SUM(OFFSET('Tab 1'!I7,,,'Tab 1'!COUNTA(I:I)-'Tab 1'!COUNTA(I1:I6)))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Brian" <bxxcfilm@nildram.co.uk> wrote in message
> > news:ens8ghdCFHA.3732@TK2MSFTNGP14.phx.gbl...
> > > I have to build a workbook where columns of data will be cut and
pasted
> > into
> > > Tab 1, then the column totals need to appear automatically on Tab 2.
The
> > > problem is that the number of rows of data in Tab 1 will be variable
and
> > > unpredictable, although the columns will always start in a known row.
> > >
> > > I might be able to do it by something like
> > >
> > > =SUM('Tab 1'!I7:I65535)
> > >
> > > but is there a more precise way?
> > >
> > >
> > >
> >
> >
>
>

Bob,

That works for me. Many thanks.

Brian

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:Oil$dGfCFHA.392@TK2MSFTNGP14.phx.gbl...
> Brian,
>
> Sorry my mistake, I added the sheets at the end,wrongly. Try this
>
> =SUM(OFFSET('Tab 1'!I7,,,COUNTA('Tab 1'!I:I)-COUNTA('Tab 1'!I1:I6)))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Brian" <bxxcfilm@nildram.co.uk> wrote in message
> news:OZbk0neCFHA.2384@TK2MSFTNGP14.phx.gbl...
> > Bob,
> >
> > This generates an error. The "Tab 1" after the 3 commas is highlighted.
I
> am
> > using XL2000.
> >
> > Brian
> >
> >
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:eKAuMqdCFHA.1836@tk2msftngp13.phx.gbl...
> > > Here is one way
> > >
> > > =SUM(OFFSET('Tab 1'!I7,,,'Tab 1'!COUNTA(I:I)-'Tab 1'!COUNTA(I1:I6)))
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Brian" <bxxcfilm@nildram.co.uk> wrote in message
> > > news:ens8ghdCFHA.3732@TK2MSFTNGP14.phx.gbl...
> > > > I have to build a workbook where columns of data will be cut and
> pasted
> > > into
> > > > Tab 1, then the column totals need to appear automatically on Tab 2.
> The
> > > > problem is that the number of rows of data in Tab 1 will be variable
> and
> > > > unpredictable, although the columns will always start in a known
row.
> > > >
> > > > I might be able to do it by something like
> > > >
> > > > =SUM('Tab 1'!I7:I65535)
> > > >
> > > > but is there a more precise way?
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

If it's the whole column you want the total for you can use:

=SUM('Tab 1'!I:I)

"Brian" wrote:

> I have to build a workbook where columns of data will be cut and pasted into
> Tab 1, then the column totals need to appear automatically on Tab 2. The
> problem is that the number of rows of data in Tab 1 will be variable and
> unpredictable, although the columns will always start in a known row.
>
> I might be able to do it by something like
>
> =SUM('Tab 1'!I7:I65535)
>
> but is there a more precise way?
>
>
>
>