# Thread: sum columns based on (2003)

1. ## 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

2. ## 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. ## 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. ## 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)
wss.Range("A1:G1").Copy wst.Range("A1")
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>

5. ## Re: sum columns based on (2003)

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

6. ## 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.

Dim myRange As Range, i As Long, lCol As Long, j As Long
Set myRange = Range("a1").CurrentRegion
lCol = myRange.Columns.Count
i = 1
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
•