[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 312

# excel macro to sum values

I have data in excel sheet1 like this

``````ITEMNO	DESCRIPTION	PERSON	QTY	RATE	VALUE
123	descr1  	per1	10	10	100
123	descr1  	per2	20	11	220
123	descr1  	per3	30	12	360
234	descr2  	per1	10	9	90
234	descr2  	per2	10	8	80
345	descr3  	per3	20	7	140
345	descr3  	per4	10	6	60
``````

And I need the sum of values in sheet2 like this

``````ITEMNO	DESCRIPTION	TOT VAL
123	descr1  	680
234	descr2  	170
345	descr3  	200
``````

I need a macro to do this summation based on ITEMNO
sample.xls
0
karunamoorthy
1 Solution

Commented:
Why do you think you need a macro. Wouldn't a pivot table provide you the information you need? See attached file for pivot sample.
sample.xls
0

Author Commented:
it would be better to have a macro for my question pl
0

Commented:
Hi,

Try this.

``````Sub kTest()

Dim ka, i As Long, x

ka = Worksheets("Sheet1").Range("a1").CurrentRegion.Resize(, 6).Value2

With CreateObject("scripting.dictionary")
.comparemode = 1
For i = 2 To UBound(ka, 1)
x = .Item(ka(i, 1))
If IsArray(x) Then
.Item(ka(i, 1)) = Array(ka(i, 2), x(1) + ka(i, 6))
Else
.Item(ka(i, 1)) = Array(ka(i, 2), ka(i, 6))
End If
Next
If .Count Then
Worksheets("Sheet2").Range("a1:c1") = Application.Index(ka, 1, Array(1, 2, 6))
Worksheets("Sheet2").Range("a2").Resize(.Count) = Application.Transpose(.keys)
Worksheets("Sheet2").Range("b2").Resize(.Count, 2) = Application.Transpose(Application.Transpose(.items))
End If
End With

End Sub
``````

Kris
0

IT & Database AssistantCommented:
Or you can use the SUMIF function, in sheet2 column C

=SUMIF(Sheet1!\$A:\$A,\$A1,Sheet1!\$F:\$F)

Where A1 contains the ITEMNO and column B contains the Item Description, although not required for above.

Copy this down to required rows and A1 will adjust to new row.

Thanks
Rob H
0

Author Commented:
>>krishnakrkc,
The macro given by you works great! Could you pl give explanation or comment how it works, I have very little knowledge regarding excel macro.
0

Commented:
0

Author Commented:
Thank you very much
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.