sum last rows

[geebee]

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

 

[Gary''s Student]

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

 

[Rick Rothstein]

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?

 

[Don Guillett]

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

 

[geebee]

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

 

[Gary''s Student]

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

 

[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]

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

 

[Don Guillett]

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

 

[Rick Rothstein]

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)

 

[Don Guillett]

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

 

[Rick Rothstein]

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.

 

[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 there
=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

 

[geebee]

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

thanks in advance,
geebee

 

[Don Guillett]

Tested and it missed the last column