Results 1 to 8 of 8

Thread: Sumif

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a very long Excel spreadsheet (>4300 rows by approx 30 columns) that consists of >400 invoices. At the top left hand cell of each invoice is the contract number, and at the last cell in column K of each invoice is the number of widgets that were sold, and at the last cell in column O of each invoice is the amount that is due ($). Some of the invoices fit on a single page, while others are multi-page.

    What I need to do on a separate spreadsheet is sum, by contract, both the number of widgets sold, as well as the amounts due.

    Any ideas?

    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Could you post a small sample workbook with dummy data so that we have an idea what the data look like?

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779876' date='15-Jun-2009 08:58']Could you post a small sample workbook with dummy data so that we have an idea what the data look like?[/quote]

    Here you go...
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here is a macro you could use:

    Code:
    Sub CreateSummary()
      Dim wshSrc As Worksheet
      Dim wshTrg As Worksheet
      Dim oCell As Range
      Dim r As Long
      If MsgBox("Existing data on the summary sheet will be cleared." & vbCrLf & _
    	  "Are you sure?", vbQuestion + vbOKCancel) = vbCancel Then
    	Exit Sub
      End If
      ' Adjust as needed
      Set wshSrc = Worksheets("Sheet1")
      Set wshTrg = Worksheets("Sheet2")
      wshTrg.Range("2:" & wshTrg.Rows.Count).ClearContents
      r = 1
      Set oCell = wshSrc.Cells(wshSrc.Rows.Count, 15).End(xlUp)
      Do While oCell.Row > 1
    	r = r + 1
    	wshTrg.Cells(r, 1) = oCell.Offset(0, -14).End(xlUp)
    	wshTrg.Cells(r, 2) = oCell.Offset(0, -4)
    	wshTrg.Cells(r, 3) = oCell
    	Set oCell = oCell.End(xlUp)
      Loop
      ' Optional: sort target sheet
      wshTrg.UsedRange.Sort Key1:=wshTrg.Cells(1, 1), Header:=xlYes
    End Sub
    See attached workbook (now with extension .xlsm, zipped)
    Attached Files Attached Files

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Hans

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779918' date='15-Jun-2009 11:07']Here is a macro you could use:

    Code:
    Sub CreateSummary()
      Dim wshSrc As Worksheet
      Dim wshTrg As Worksheet
      Dim oCell As Range
      Dim r As Long
      If MsgBox("Existing data on the summary sheet will be cleared." & vbCrLf & _
    	  "Are you sure?", vbQuestion + vbOKCancel) = vbCancel Then
    	Exit Sub
      End If
      ' Adjust as needed
      Set wshSrc = Worksheets("Sheet1")
      Set wshTrg = Worksheets("Sheet2")
      wshTrg.Range("2:" & wshTrg.Rows.Count).ClearContents
      r = 1
      Set oCell = wshSrc.Cells(wshSrc.Rows.Count, 15).End(xlUp)
      Do While oCell.Row > 1
    	r = r + 1
    	wshTrg.Cells(r, 1) = oCell.Offset(0, -14).End(xlUp)
    	wshTrg.Cells(r, 2) = oCell.Offset(0, -4)
    	wshTrg.Cells(r, 3) = oCell
    	Set oCell = oCell.End(xlUp)
      Loop
      ' Optional: sort target sheet
      wshTrg.UsedRange.Sort Key1:=wshTrg.Cells(1, 1), Header:=xlYes
    End Sub
    See attached workbook (now with extension .xlsm, zipped)[/quote]

    Hans,
    If I may impose upon you, I am trying to learn VBA, and would very much appresiate if you would walk through your solution so that I might become more proficient.
    Thanks.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The essential part, line by line:

    ' Clear the existing summary in Sheet2 (except for row 1 with the column headers)
    wshTrg.Range("2:" & wshTrg.Rows.Count).ClearContents
    ' We initialize the row number to be used on Sheet2 to 1
    r = 1
    ' Set oCell to the last filled cell in column O (= column 15) on Sheet1
    Set oCell = wshSrc.Cells(wshSrc.Rows.Count, 15).End(xlUp)
    ' Loop while we find a filled cell
    Do While oCell.Row > 1
    ' Increase the row number for Sheet2
    r = r + 1
    ' Set the cell in column A on Sheet2 to the contract name (go 14 columns to the left, i.e. to column A, then look upwards)
    wshTrg.Cells(r, 1) = oCell.Offset(0, -14).End(xlUp)
    ' Set the cell in column B on Sheet2 to the number of widgets (4 columns to the left, i.e. in column K).
    wshTrg.Cells(r, 2) = oCell.Offset(0, -4)
    ' Set the cell in column C on Sheet2 to the amount from column O.
    wshTrg.Cells(r, 3) = oCell
    ' Move upwards to the next filled cell in column O.
    Set oCell = oCell.End(xlUp)
    ' And loop
    Loop

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks again.

Posting Permissions

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