Thread: Sumif

1. 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. Could you post a small sample workbook with dummy data so that we have an idea what the data look like?

3. [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...

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

5. Thanks Hans

6. [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. 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. 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
•