Sum a column of variable length?

B

Brian

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?
 
B

Bob Phillips

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)
 
B

Brian

Bob,

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

Brian
 
B

Bob Phillips

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)
 
B

Brian

Bob,

That works for me. Many thanks.

Brian

Bob Phillips said:
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)


I
Click to expand...
 
Top