Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sum columns based on (2003)

    Hi all
    I have a very large spreadsheet --based on value found in column "A" I need to total all adjancent columns see attached file- sheet 1 is required output and sheet 2 is sample snippet for input
    Many thanx
    Smbs
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sum columns based on (2003)

    Try this formula in C1:

    =SUMIF(Sheet2!$B:$B,TRIM(Sheet1!$B1),Sheet2!C:C)

    Fill right, then down (or down, then right)

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sum columns based on (2003)

    thanx Hans that works because I have already have sheet 1 ready (this sheet was prepared just as an example to show what I require)with unique values in column A -- I really need a macro which will create the entire new sheet 1 and get the required summary based on unique values in column A sheet 2
    thanx
    Smbs

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sum columns based on (2003)

    I'd insert a row with field names (column headers) above the data. You can then use advanced filter - see the attached workbook. The code is:
    <code>
    Sub CreateSummary()
    Const strSource = "Sheet2"
    Dim wss As Worksheet
    Dim wst As Worksheet
    Dim m As Long
    Set wss = Worksheets(strSource)
    Set wst = Worksheets.Add
    wss.Range("A1:G1").Copy wst.Range("A1")
    wss.Range("A1").CurrentRegion.AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:=wss.Range("A1"), _
    CopyToRange:=wst.Range("A1:B1"), Unique:=True
    m = wst.Cells(wst.Rows.Count, 1).End(xlUp).Row
    wst.Range("C2:G" & m).Formula = "=SUMIF('" & strSource & _
    "'!$B:$B,$B2,'" & strSource & "'!C:C)"
    End Sub</code>
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sum columns based on (2003)

    many thanx Hans--thats exactly what I need
    Regards
    Smbs

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sum columns based on (2003)

    Assuming your data is exactly as you have it on Sheet2 - that is with no headers.

    WARNING!!! Your original data is replaced with the summary information.

    <pre>Sub addemUp()
    Dim myRange As Range, i As Long, lCol As Long, j As Long
    Set myRange = Range("a1").CurrentRegion
    lCol = myRange.Columns.Count
    i = 1
    myRange.Sort key1:=Range("a1"), order1:=xlAscending, header:=xlNo
    Application.ScreenUpdating = False

    Do
    If Cells(i, 1) = Cells(i + 1, 1) Then
    For j = 3 To lCol
    Cells(i, j) = Cells(i + 1, j) + Cells(i, j)

    Next
    Cells(i + 1, 1).EntireRow.Delete
    i = i
    Else
    i = i + 1

    End If
    If Cells(i, 1) = "" Then
    Exit Sub
    Application.ScreenUpdating = True
    End If
    Loop
    End Sub
    </pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •