PDA

View Full Version : Summing Dynamic Cells


adalton
Jun 13, 2009, 08:08 PM
Sorry for all the dumb questions :( I'm still getting used to excel.

I need to go down a column and sum some cells before it. However depending on the column this may change.

I tried using the Sum function but that requires the values to be set.

In a column the sum cells will be the same... Ie go down G sum values from A-F, however the next column may be J and it needs to sum values from H & I.

JBeaucaire
Jun 13, 2009, 10:24 PM
Sorry, the question provides no meaningful information for suggesting a formula or a macro. When something is this (apparently) difficult to explain, don't. Show us.

So mockup a sample workbook showing the dilemma and your desired results, point out where the results came from if not obvious in your sample data. If the results change with various scenarios, demonstrate multiple scenarios until every possible need is covered.

Be sure to reread your sheet from the perspective of "I didn't write this, does it make sense what I've written"?

Then click GO ADVANCED and use the paperclip icon to post up your workbook.

adalton
Jun 14, 2009, 11:06 AM
So right now it goes through until it finds a SUM column. I want it to SUM all the columns up until the last SUM column.

The bold is what I want the macro to do.

adalton
Jun 14, 2009, 11:08 AM
Sorry here's the sample.

JBeaucaire
Jun 14, 2009, 03:09 PM
Here's a macro to do what you wish. I took out the row 1 labels since they were redundant. Just put your data in row1.

Option Explicit

Sub AddRowTotals()
Dim LR As Long, FC As Long, LC As Long, i As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
LC = Cells(1, Columns.Count).End(xlToLeft).Column + 1
FC = 1

'Determine columns to be summed, insert formula
For i = 1 To LC
If Cells(2, i) = "" Then
If Cells(2, i - 1).HasFormula Then Exit Sub
With Range(Cells(1, i), Cells(LR, i))
.FormulaR1C1 = "=SUM(RC" & FC & ":RC" & LC - 1 & ")"
.Font.Bold = True
End With

ElseIf Cells(2, i).HasFormula = True And IsNumeric(Cells(2, i + 1)) Then
FC = i + 1
End If

Next i

End Sub

ScottGem
Jun 14, 2009, 03:21 PM
I'm not sure if a macro is necessary here. I think your problem is that if you use the AUTOSUM feature it will automatically try to sum all the contiguous columns, So if use the
AutoSum in Column G, it will sum A-F. But if you only want it to sum D-F, you can manually adjust the formula or type I in:

=Sum(D2:F2)

JBeaucaire
Jun 14, 2009, 05:31 PM
I thought the same thing, then realized he just wants the sums added quickly/easily without having to do any of the figuring out each time. Understandable.

adalton
Jun 14, 2009, 08:34 PM
Thanks. Your code worked beautifully JBeaucaire