Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Summing Dynamic Cells (https://www.askmehelpdesk.com/showthread.php?t=364684)

  • Jun 13, 2009, 08:08 PM
    adalton
    Summing Dynamic Cells
    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.
  • Jun 13, 2009, 10:24 PM
    JBeaucaire

    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.
  • Jun 14, 2009, 11:06 AM
    adalton
    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.
  • Jun 14, 2009, 11:08 AM
    adalton
    1 Attachment(s)
    Sorry here's the sample.
  • Jun 14, 2009, 03:09 PM
    JBeaucaire
    1 Attachment(s)

    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.
    Code:

    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

  • Jun 14, 2009, 03:21 PM
    ScottGem

    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)
  • Jun 14, 2009, 05:31 PM
    JBeaucaire

    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.
  • Jun 14, 2009, 08:34 PM
    adalton
    Thanks. Your code worked beautifully JBeaucaire

  • All times are GMT -7. The time now is 11:26 AM.