hi,

i have lots of columns. columns are added each month. is there a way to
dynamically sum the last 3 columns to the right of the very last column for
each row?

thanks in advance,
geebee

Say A1 thru G1 contain:
1 2 3 4 5 6 7

and we want to sum the last three columns to the right of the last column.
We need to add: 6 + 5 + 4

Sub gebe()
n = Cells(1, Columns.Count).End(xlToLeft).Column
v = Cells(1, n - 3) + Cells(1, n - 2) + Cells(1, n - 1)
MsgBox (v)
End Sub

--
Gary''s Student - gsnu200820


"geebee" wrote:

hi,

i have lots of columns. columns are added each month. is there a way to
dynamically sum the last 3 columns to the right of the very last column for
each row?

thanks in advance,
geebee

Two questions...

1. When you said "to the right", did you actually mean "to the LEFT" of the
very last column? That is, for any given row, if you had data in columns
1,2,3,4,5,6... did you want the sum of columns 3,4,5 (ignoring the data in
column 6)?

2. Where did you want these row-sums at? On the worksheet? In an array in
memory? Somewhere else?

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

i have lots of columns. columns are added each month. is there a way to
dynamically sum the last 3 columns to the right of the very last column
for
each row?

thanks in advance,
geebee

Sub sumlastthreecolumns()
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
lc = Cells(i, Columns.Count).End(xlToLeft).Column - 2
If lc 0 Then
'MsgBox lc
MsgBox WorksheetFunction.Sum(Cells(i, lc).Resize(, 3))
End If
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"geebee" (noSPAMs) wrote in message
...
hi,

i have lots of columns. columns are added each month. is there a way to
dynamically sum the last 3 columns to the right of the very last column
for
each row?

thanks in advance,
geebee

hi...
how can i refer to this from within another sheet?
thanks in advance,
geebee


"Gary''s Student" wrote:

Say A1 thru G1 contain:
1 2 3 4 5 6 7

and we want to sum the last three columns to the right of the last column.
We need to add: 6 + 5 + 4

Sub gebe()
n = Cells(1, Columns.Count).End(xlToLeft).Column
v = Cells(1, n - 3) + Cells(1, n - 2) + Cells(1, n - 1)
MsgBox (v)
End Sub

--
Gary''s Student - gsnu200820


"geebee" wrote:

hi,

i have lots of columns. columns are added each month. is there a way to
dynamically sum the last 3 columns to the right of the very last column for
each row?

thanks in advance,
geebee

Use With:

Sub gebe()
With Sheets("Sheet7")
n = .Cells(1, Columns.Count).End(xlToLeft).Column
v = .Cells(1, n - 3) + .Cells(1, n - 2) + .Cells(1, n - 1)
End With
MsgBox (v)
End Sub

--
Gary''s Student - gsnu200820


"geebee" wrote:

hi...
how can i refer to this from within another sheet?
thanks in advance,
geebee


"Gary''s Student" wrote:

Say A1 thru G1 contain:
1 2 3 4 5 6 7

and we want to sum the last three columns to the right of the last column.
We need to add: 6 + 5 + 4

Sub gebe()
n = Cells(1, Columns.Count).End(xlToLeft).Column
v = Cells(1, n - 3) + Cells(1, n - 2) + Cells(1, n - 1)
MsgBox (v)
End Sub

--
Gary''s Student - gsnu200820


"geebee" wrote:

hi,

i have lots of columns. columns are added each month. is there a way to
dynamically sum the last 3 columns to the right of the very last column for
each row?

thanks in advance,
geebee

hi,

yes i meant to the right. so if i have columns 1 2 3 4 5 and 6 i want the
sum of columns 3 4 and 5, for every row. i want the sums in a column in
another sheet in which we have different names in each row... so i want the
column sum to show up for each name. i guess i could have the sums in the
same sheet the columns are in, and then do a vlookup in the other sheet to
get the column totals for each name. how do i go about getting the totals
for each row?
maybe im not getting it... or its getting late for me.

thanks in advance,
geebee


"Rick Rothstein" wrote:

Two questions...

1. When you said "to the right", did you actually mean "to the LEFT" of the
very last column? That is, for any given row, if you had data in columns
1,2,3,4,5,6... did you want the sum of columns 3,4,5 (ignoring the data in
column 6)?

2. Where did you want these row-sums at? On the worksheet? In an array in
memory? Somewhere else?

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

i have lots of columns. columns are added each month. is there a way to
dynamically sum the last 3 columns to the right of the very last column
for
each row?

thanks in advance,
geebee

Put this formula on your "other" sheet and copy it down...

=SUMPRODUCT((COLUMN('Data Sheet'!1:1)MAX(COLUMN('Data Sheet'!1:1)*('Data
Sheet'!1:1<""))-4)*('Data Sheet'!1:1))-LOOKUP(2,1/('Data
Sheet'!1:1<""),'Data Sheet'!1:1)

Note: Change my 'Data Sheet' to the name of your sheet with the columns you
are adding (use apostrophes around the name if it contains spaces, otherwise
they are not needed). Also, all those 1:1 refer to Row 1... if your data
starts on a different row, then use that row number in place of the 1s.

For future reference, it is always a good idea to tell us sheet names along
with row and column references so we don't have to use made up names and
references (easier for you too as you wouldn't have to change anything if we
could use your actual names and references).

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

yes i meant to the right. so if i have columns 1 2 3 4 5 and 6 i want the
sum of columns 3 4 and 5, for every row. i want the sums in a column in
another sheet in which we have different names in each row... so i want
the
column sum to show up for each name. i guess i could have the sums in the
same sheet the columns are in, and then do a vlookup in the other sheet to
get the column totals for each name. how do i go about getting the totals
for each row?
maybe im not getting it... or its getting late for me.

thanks in advance,
geebee


"Rick Rothstein" wrote:

Two questions...

1. When you said "to the right", did you actually mean "to the LEFT" of
the
very last column? That is, for any given row, if you had data in columns
1,2,3,4,5,6... did you want the sum of columns 3,4,5 (ignoring the data
in
column 6)?

2. Where did you want these row-sums at? On the worksheet? In an array in
memory? Somewhere else?

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

i have lots of columns. columns are added each month. is there a way
to
dynamically sum the last 3 columns to the right of the very last column
for
each row?

thanks in advance,
geebee

I guess you didn't see mine.

Sub sumlastthreecolumns()
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
lc = Cells(i, Columns.Count).End(xlToLeft).Column - 2
If lc 0 Then
'MsgBox lc
'MsgBox WorksheetFunction.Sum(Cells(i, lc).Resize(, 3))

sheets("othersheetnamehere").cells(i+1,1)= _
WorksheetFunction.Sum(Cells(i, lc).Resize(, 3))

End If
Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"geebee" (noSPAMs) wrote in message
...
hi,

yes i meant to the right. so if i have columns 1 2 3 4 5 and 6 i want the
sum of columns 3 4 and 5, for every row. i want the sums in a column in
another sheet in which we have different names in each row... so i want
the
column sum to show up for each name. i guess i could have the sums in the
same sheet the columns are in, and then do a vlookup in the other sheet to
get the column totals for each name. how do i go about getting the totals
for each row?
maybe im not getting it... or its getting late for me.

thanks in advance,
geebee


"Rick Rothstein" wrote:

Two questions...

1. When you said "to the right", did you actually mean "to the LEFT" of
the
very last column? That is, for any given row, if you had data in columns
1,2,3,4,5,6... did you want the sum of columns 3,4,5 (ignoring the data
in
column 6)?

2. Where did you want these row-sums at? On the worksheet? In an array in
memory? Somewhere else?

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

i have lots of columns. columns are added each month. is there a way
to
dynamically sum the last 3 columns to the right of the very last column
for
each row?

thanks in advance,
geebee

Just a quick note to say my newsreader broke the formula apart at (required)
blank spaces. Here is the formula manually broken apart to avoid that...

=SUMPRODUCT((COLUMN('Data Sheet'!1:1)MAX(COLUMN('Data Sheet'!1:1)*
('Data Sheet'!1:1<""))-4)*('Data Sheet'!1:1))-LOOKUP(2,1/
('Data Sheet'!1:1<""),'Data Sheet'!1:1)

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Put this formula on your "other" sheet and copy it down...

=SUMPRODUCT((COLUMN('Data Sheet'!1:1)MAX(COLUMN('Data Sheet'!1:1)*('Data
Sheet'!1:1<""))-4)*('Data Sheet'!1:1))-LOOKUP(2,1/('Data
Sheet'!1:1<""),'Data Sheet'!1:1)

Note: Change my 'Data Sheet' to the name of your sheet with the columns
you are adding (use apostrophes around the name if it contains spaces,
otherwise they are not needed). Also, all those 1:1 refer to Row 1... if
your data starts on a different row, then use that row number in place of
the 1s.

For future reference, it is always a good idea to tell us sheet names
along with row and column references so we don't have to use made up names
and references (easier for you too as you wouldn't have to change anything
if we could use your actual names and references).

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

yes i meant to the right. so if i have columns 1 2 3 4 5 and 6 i want
the
sum of columns 3 4 and 5, for every row. i want the sums in a column in
another sheet in which we have different names in each row... so i want
the
column sum to show up for each name. i guess i could have the sums in
the
same sheet the columns are in, and then do a vlookup in the other sheet
to
get the column totals for each name. how do i go about getting the
totals
for each row?
maybe im not getting it... or its getting late for me.

thanks in advance,
geebee


"Rick Rothstein" wrote:

Two questions...

1. When you said "to the right", did you actually mean "to the LEFT" of
the
very last column? That is, for any given row, if you had data in columns
1,2,3,4,5,6... did you want the sum of columns 3,4,5 (ignoring the data
in
column 6)?

2. Where did you want these row-sums at? On the worksheet? In an array
in
memory? Somewhere else?

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

i have lots of columns. columns are added each month. is there a way
to
dynamically sum the last 3 columns to the right of the very last
column
for
each row?

thanks in advance,
geebee

Sub FixLongFormulas() 'goto a remote area of ws & select 1st line
X = ActiveCell.Row
y = ActiveCell.Column
z = ActiveCell.End(xlDown).Row
For Each C In Range(Cells(X, y), Cells(z, y))
mstr = mstr & C
Next
Cells(X - 1, y) = mstr
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
Just a quick note to say my newsreader broke the formula apart at
(required) blank spaces. Here is the formula manually broken apart to
avoid that...

=SUMPRODUCT((COLUMN('Data Sheet'!1:1)MAX(COLUMN('Data Sheet'!1:1)*
('Data Sheet'!1:1<""))-4)*('Data Sheet'!1:1))-LOOKUP(2,1/
('Data Sheet'!1:1<""),'Data Sheet'!1:1)

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Put this formula on your "other" sheet and copy it down...

=SUMPRODUCT((COLUMN('Data Sheet'!1:1)MAX(COLUMN('Data Sheet'!1:1)*('Data
Sheet'!1:1<""))-4)*('Data Sheet'!1:1))-LOOKUP(2,1/('Data
Sheet'!1:1<""),'Data Sheet'!1:1)

Note: Change my 'Data Sheet' to the name of your sheet with the columns
you are adding (use apostrophes around the name if it contains spaces,
otherwise they are not needed). Also, all those 1:1 refer to Row 1... if
your data starts on a different row, then use that row number in place of
the 1s.

For future reference, it is always a good idea to tell us sheet names
along with row and column references so we don't have to use made up
names and references (easier for you too as you wouldn't have to change
anything if we could use your actual names and references).

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

yes i meant to the right. so if i have columns 1 2 3 4 5 and 6 i want
the
sum of columns 3 4 and 5, for every row. i want the sums in a column in
another sheet in which we have different names in each row... so i want
the
column sum to show up for each name. i guess i could have the sums in
the
same sheet the columns are in, and then do a vlookup in the other sheet
to
get the column totals for each name. how do i go about getting the
totals
for each row?
maybe im not getting it... or its getting late for me.

thanks in advance,
geebee


"Rick Rothstein" wrote:

Two questions...

1. When you said "to the right", did you actually mean "to the LEFT" of
the
very last column? That is, for any given row, if you had data in
columns
1,2,3,4,5,6... did you want the sum of columns 3,4,5 (ignoring the data
in
column 6)?

2. Where did you want these row-sums at? On the worksheet? In an array
in
memory? Somewhere else?

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

i have lots of columns. columns are added each month. is there a
way to
dynamically sum the last 3 columns to the right of the very last
column
for
each row?

thanks in advance,
geebee

Actually, I wasn't necessarily referring to the "split across rows" problem
so much as just alerting the OP that there are supposed to be spaces between
the words "Data" and "Sheet".

Cute code... but, of course, the problem can be avoided by pasting the
copied formula into the Formula Bar directly and deleting the Line Feeds.

I think you should mention in your instruction to be sure to copy the
formula to any row except Row 1, otherwise the X-1 argument will blow up the
Cells property call.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Sub FixLongFormulas() 'goto a remote area of ws & select 1st line
X = ActiveCell.Row
y = ActiveCell.Column
z = ActiveCell.End(xlDown).Row
For Each C In Range(Cells(X, y), Cells(z, y))
mstr = mstr & C
Next
Cells(X - 1, y) = mstr
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
Just a quick note to say my newsreader broke the formula apart at
(required) blank spaces. Here is the formula manually broken apart to
avoid that...

=SUMPRODUCT((COLUMN('Data Sheet'!1:1)MAX(COLUMN('Data Sheet'!1:1)*
('Data Sheet'!1:1<""))-4)*('Data Sheet'!1:1))-LOOKUP(2,1/
('Data Sheet'!1:1<""),'Data Sheet'!1:1)

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Put this formula on your "other" sheet and copy it down...

=SUMPRODUCT((COLUMN('Data Sheet'!1:1)MAX(COLUMN('Data
Sheet'!1:1)*('Data Sheet'!1:1<""))-4)*('Data
Sheet'!1:1))-LOOKUP(2,1/('Data Sheet'!1:1<""),'Data Sheet'!1:1)

Note: Change my 'Data Sheet' to the name of your sheet with the columns
you are adding (use apostrophes around the name if it contains spaces,
otherwise they are not needed). Also, all those 1:1 refer to Row 1... if
your data starts on a different row, then use that row number in place
of the 1s.

For future reference, it is always a good idea to tell us sheet names
along with row and column references so we don't have to use made up
names and references (easier for you too as you wouldn't have to change
anything if we could use your actual names and references).

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

yes i meant to the right. so if i have columns 1 2 3 4 5 and 6 i want
the
sum of columns 3 4 and 5, for every row. i want the sums in a column
in
another sheet in which we have different names in each row... so i want
the
column sum to show up for each name. i guess i could have the sums in
the
same sheet the columns are in, and then do a vlookup in the other sheet
to
get the column totals for each name. how do i go about getting the
totals
for each row?
maybe im not getting it... or its getting late for me.

thanks in advance,
geebee


"Rick Rothstein" wrote:

Two questions...

1. When you said "to the right", did you actually mean "to the LEFT"
of the
very last column? That is, for any given row, if you had data in
columns
1,2,3,4,5,6... did you want the sum of columns 3,4,5 (ignoring the
data in
column 6)?

2. Where did you want these row-sums at? On the worksheet? In an array
in
memory? Somewhere else?

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

i have lots of columns. columns are added each month. is there a
way to
dynamically sum the last 3 columns to the right of the very last
column
for
each row?

thanks in advance,
geebee

hi,

i would actually like to try to use this formula in my "NEWDATA" sheet in
first empty column to the right of all the data already in the
=SUM(OFFSET($A$1,ROW()-1,MATCH(LEFT(TEXT((MONTH(Data!$BQ$1)-2)*29,"mmm")&"-"&RIGHT(YEAR(Data!$BQ$1),2),4)&TEXT(YEAR(TODAY()), "YY"),$1:$1,1)-1,1,1):OFFSET(AN108,0,0))

in the "data" sheet cell BQ1 there is a value of Nov-08 determined by a
formula: =TEXT(BN1+0,"mmm yy"), with a value of Nov-08 stored as date in
cell BN1 of "Data" sheet, stored in the "Mar-01" date format.

In the "NEWDATA" sheet there are values like "Sep-08", "Oct-08" and so forth
in the first row 1 across the columns, also stored in the "Mar-01" date
format.

The preceding formula attempts to sum up values for each row for all months
in the current year which are within the past 3 months (sum up all values for
each row in which the month names for the columns are in the past 3
months)... its just not working right.

im not sure how to amend this formula i am trying to use.

thanks in advance,
geebee


"Rick Rothstein" wrote:

Actually, I wasn't necessarily referring to the "split across rows" problem
so much as just alerting the OP that there are supposed to be spaces between
the words "Data" and "Sheet".

Cute code... but, of course, the problem can be avoided by pasting the
copied formula into the Formula Bar directly and deleting the Line Feeds.

I think you should mention in your instruction to be sure to copy the
formula to any row except Row 1, otherwise the X-1 argument will blow up the
Cells property call.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...
Sub FixLongFormulas() 'goto a remote area of ws & select 1st line
X = ActiveCell.Row
y = ActiveCell.Column
z = ActiveCell.End(xlDown).Row
For Each C In Range(Cells(X, y), Cells(z, y))
mstr = mstr & C
Next
Cells(X - 1, y) = mstr
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
Just a quick note to say my newsreader broke the formula apart at
(required) blank spaces. Here is the formula manually broken apart to
avoid that...

=SUMPRODUCT((COLUMN('Data Sheet'!1:1)MAX(COLUMN('Data Sheet'!1:1)*
('Data Sheet'!1:1<""))-4)*('Data Sheet'!1:1))-LOOKUP(2,1/
('Data Sheet'!1:1<""),'Data Sheet'!1:1)

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Put this formula on your "other" sheet and copy it down...

=SUMPRODUCT((COLUMN('Data Sheet'!1:1)MAX(COLUMN('Data
Sheet'!1:1)*('Data Sheet'!1:1<""))-4)*('Data
Sheet'!1:1))-LOOKUP(2,1/('Data Sheet'!1:1<""),'Data Sheet'!1:1)

Note: Change my 'Data Sheet' to the name of your sheet with the columns
you are adding (use apostrophes around the name if it contains spaces,
otherwise they are not needed). Also, all those 1:1 refer to Row 1... if
your data starts on a different row, then use that row number in place
of the 1s.

For future reference, it is always a good idea to tell us sheet names
along with row and column references so we don't have to use made up
names and references (easier for you too as you wouldn't have to change
anything if we could use your actual names and references).

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

yes i meant to the right. so if i have columns 1 2 3 4 5 and 6 i want
the
sum of columns 3 4 and 5, for every row. i want the sums in a column
in
another sheet in which we have different names in each row... so i want
the
column sum to show up for each name. i guess i could have the sums in
the
same sheet the columns are in, and then do a vlookup in the other sheet
to
get the column totals for each name. how do i go about getting the
totals
for each row?
maybe im not getting it... or its getting late for me.

thanks in advance,
geebee


"Rick Rothstein" wrote:

Two questions...

1. When you said "to the right", did you actually mean "to the LEFT"
of the
very last column? That is, for any given row, if you had data in
columns
1,2,3,4,5,6... did you want the sum of columns 3,4,5 (ignoring the
data in
column 6)?

2. Where did you want these row-sums at? On the worksheet? In an array
in
memory? Somewhere else?

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

i have lots of columns. columns are added each month. is there a
way to
dynamically sum the last 3 columns to the right of the very last
column
for
each row?

thanks in advance,
geebee

hi,
can you send me a file containing this formula so i can have example? im
not getting it.

thanks in advance,
geebee


"Rick Rothstein" wrote:

Just a quick note to say my newsreader broke the formula apart at (required)
blank spaces. Here is the formula manually broken apart to avoid that...

=SUMPRODUCT((COLUMN('Data Sheet'!1:1)MAX(COLUMN('Data Sheet'!1:1)*
('Data Sheet'!1:1<""))-4)*('Data Sheet'!1:1))-LOOKUP(2,1/
('Data Sheet'!1:1<""),'Data Sheet'!1:1)

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Put this formula on your "other" sheet and copy it down...

=SUMPRODUCT((COLUMN('Data Sheet'!1:1)MAX(COLUMN('Data Sheet'!1:1)*('Data
Sheet'!1:1<""))-4)*('Data Sheet'!1:1))-LOOKUP(2,1/('Data
Sheet'!1:1<""),'Data Sheet'!1:1)

Note: Change my 'Data Sheet' to the name of your sheet with the columns
you are adding (use apostrophes around the name if it contains spaces,
otherwise they are not needed). Also, all those 1:1 refer to Row 1... if
your data starts on a different row, then use that row number in place of
the 1s.

For future reference, it is always a good idea to tell us sheet names
along with row and column references so we don't have to use made up names
and references (easier for you too as you wouldn't have to change anything
if we could use your actual names and references).

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

yes i meant to the right. so if i have columns 1 2 3 4 5 and 6 i want
the
sum of columns 3 4 and 5, for every row. i want the sums in a column in
another sheet in which we have different names in each row... so i want
the
column sum to show up for each name. i guess i could have the sums in
the
same sheet the columns are in, and then do a vlookup in the other sheet
to
get the column totals for each name. how do i go about getting the
totals
for each row?
maybe im not getting it... or its getting late for me.

thanks in advance,
geebee


"Rick Rothstein" wrote:

Two questions...

1. When you said "to the right", did you actually mean "to the LEFT" of
the
very last column? That is, for any given row, if you had data in columns
1,2,3,4,5,6... did you want the sum of columns 3,4,5 (ignoring the data
in
column 6)?

2. Where did you want these row-sums at? On the worksheet? In an array
in
memory? Somewhere else?

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

i have lots of columns. columns are added each month. is there a way
to
dynamically sum the last 3 columns to the right of the very last
column
for
each row?

thanks in advance,
geebee

Tested and it missed the last column

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
Just a quick note to say my newsreader broke the formula apart at
(required) blank spaces. Here is the formula manually broken apart to
avoid that...

=SUMPRODUCT((COLUMN('Data Sheet'!1:1)MAX(COLUMN('Data Sheet'!1:1)*
('Data Sheet'!1:1<""))-4)*('Data Sheet'!1:1))-LOOKUP(2,1/
('Data Sheet'!1:1<""),'Data Sheet'!1:1)

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Put this formula on your "other" sheet and copy it down...

=SUMPRODUCT((COLUMN('Data Sheet'!1:1)MAX(COLUMN('Data Sheet'!1:1)*('Data
Sheet'!1:1<""))-4)*('Data Sheet'!1:1))-LOOKUP(2,1/('Data
Sheet'!1:1<""),'Data Sheet'!1:1)

Note: Change my 'Data Sheet' to the name of your sheet with the columns
you are adding (use apostrophes around the name if it contains spaces,
otherwise they are not needed). Also, all those 1:1 refer to Row 1... if
your data starts on a different row, then use that row number in place of
the 1s.

For future reference, it is always a good idea to tell us sheet names
along with row and column references so we don't have to use made up
names and references (easier for you too as you wouldn't have to change
anything if we could use your actual names and references).

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

yes i meant to the right. so if i have columns 1 2 3 4 5 and 6 i want
the
sum of columns 3 4 and 5, for every row. i want the sums in a column in
another sheet in which we have different names in each row... so i want
the
column sum to show up for each name. i guess i could have the sums in
the
same sheet the columns are in, and then do a vlookup in the other sheet
to
get the column totals for each name. how do i go about getting the
totals
for each row?
maybe im not getting it... or its getting late for me.

thanks in advance,
geebee


"Rick Rothstein" wrote:

Two questions...

1. When you said "to the right", did you actually mean "to the LEFT" of
the
very last column? That is, for any given row, if you had data in
columns
1,2,3,4,5,6... did you want the sum of columns 3,4,5 (ignoring the data
in
column 6)?

2. Where did you want these row-sums at? On the worksheet? In an array
in
memory? Somewhere else?

--
Rick (MVP - Excel)


"geebee" (noSPAMs) wrote in message
...
hi,

i have lots of columns. columns are added each month. is there a
way to
dynamically sum the last 3 columns to the right of the very last
column
for
each row?

thanks in advance,
geebee