Sum the amounts of lines with the same criteria

maxon

New Member
Joined
Oct 28, 2015
Messages
43
Hello Guys,

I need your support how to build the code properly to fix below problem.

I need to sum the amounts of lines, where "Name" and "ID number" are the same. Below you find an extract of table, normally its much bigger about 500-600 lines.

[TABLE="width: 489"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Code[/TD]
[TD]ID number[/TD]
[TD]Amount[/TD]
[TD]Ccy[/TD]
[TD]Date[/TD]
[TD]Sum[/TD]
[/TR]
[TR]
[TD]ClientA[/TD]
[TD]MD554[/TD]
[TD]AABBCCDD[/TD]
[TD] 4,052.80[/TD]
[TD]USD[/TD]
[TD]09/01/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ClientA[/TD]
[TD]MD557[/TD]
[TD]AABBCCDD[/TD]
[TD] 6,543.50[/TD]
[TD]USD[/TD]
[TD]09/01/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ClientA[/TD]
[TD]MD558[/TD]
[TD]AABBCCDD[/TD]
[TD] 8,198.08[/TD]
[TD]USD[/TD]
[TD]09/01/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ClientA[/TD]
[TD]MD567[/TD]
[TD]AABBCCDD[/TD]
[TD] 8,967.30[/TD]
[TD]USD[/TD]
[TD]09/01/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ClientA[/TD]
[TD]MD568[/TD]
[TD]AABBCCDD[/TD]
[TD] 5,011.87[/TD]
[TD]USD[/TD]
[TD]09/01/2017[/TD]
[TD] 32,773.55[/TD]
[/TR]
[TR]
[TD]ClientA[/TD]
[TD]MD567[/TD]
[TD]ABCD[/TD]
[TD] 6,475.00[/TD]
[TD]USD[/TD]
[TD]09/01/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ClientA[/TD]
[TD]MD569[/TD]
[TD]ABCD[/TD]
[TD] 11,100.00[/TD]
[TD]USD[/TD]
[TD]09/01/2017[/TD]
[TD] 17,575.00[/TD]
[/TR]
[TR]
[TD]ClientA[/TD]
[TD]MD567[/TD]
[TD]AABBCC[/TD]
[TD] 1,554.14[/TD]
[TD]USD[/TD]
[TD]09/01/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ClientA[/TD]
[TD]MD568[/TD]
[TD]AABBCC[/TD]
[TD] 806.22[/TD]
[TD]USD[/TD]
[TD]09/01/2017[/TD]
[TD] 2,360.36[/TD]
[/TR]
[TR]
[TD]ClientA[/TD]
[TD]MD567[/TD]
[TD]ABCDEF[/TD]
[TD] 340.18[/TD]
[TD]USD[/TD]
[TD]09/01/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ClientA[/TD]
[TD]MD568[/TD]
[TD]ABCDEF[/TD]
[TD] 227.15[/TD]
[TD]USD[/TD]
[TD]09/01/2017[/TD]
[TD] 567.33[/TD]
[/TR]
[TR]
[TD]ClientA[/TD]
[TD]MD567[/TD]
[TD]ABBCCDDEE[/TD]
[TD] 3,146.53[/TD]
[TD]USD[/TD]
[TD]09/01/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ClientA[/TD]
[TD]MD567[/TD]
[TD]ABBCCDDEE[/TD]
[TD] 3,146.53[/TD]
[TD]USD[/TD]
[TD]09/01/2017[/TD]
[TD] 6,293.06[/TD]
[/TR]
[TR]
[TD]ClientA[/TD]
[TD]MD567[/TD]
[TD]ABC1[/TD]
[TD] 6,375.00[/TD]
[TD]USD[/TD]
[TD]09/01/2017[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ClientA[/TD]
[TD]MD567[/TD]
[TD]ABC4[/TD]
[TD] 7,312.50[/TD]
[TD]USD[/TD]
[TD]09/01/2017[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This column Sum has been done by me.

I would be grateful if you could tell me how should look like proper code.

Thanks!
max
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Try this for results in column "G".
Code:
[COLOR="Navy"]Sub[/COLOR] MG04Sep29
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Txt = Dn.Value & "," & Dn.Offset(, 2).Value
[COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
      .Add Txt, Array(Dn.Offset(, 3).Value, Dn)
[COLOR="Navy"]Else[/COLOR]
    Q = .Item(Txt)
        Q(0) = Q(0) + Dn.Offset(, 3).Value
        [COLOR="Navy"]Set[/COLOR] Q(1) = Dn
     .Item(Txt) = Q
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .Keys
    .Item(K)(1).Offset(, 6).Value = Format(.Item(K)(0), "#,##0.00")
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
i would use a pivot table to get all clients and ids and then the same
 
Upvote 0
Hi, you could also try a formula if your data is sorted by the "name" and "ID number":


Excel 2013/2016
ABCDEFG
1NameCodeID numberAmountCcyDateSum
2ClientAMD554AABBCCDD4,052.80USD09/01/2017 
3ClientAMD557AABBCCDD6,543.50USD09/01/2017
4ClientAMD558AABBCCDD8,198.08USD09/01/2017
5ClientAMD567AABBCCDD8,967.30USD09/01/2017
6ClientAMD568AABBCCDD5,011.87USD09/01/201732773.55
7ClientAMD567ABCD6,475.00USD09/01/2017
8ClientAMD569ABCD11,100.00USD09/01/201717575
9ClientAMD567AABBCC1,554.14USD09/01/2017
10ClientAMD568AABBCC806.22USD09/01/20172360.36
11ClientAMD567ABCDEF340.18USD09/01/2017
12ClientAMD568ABCDEF227.15USD09/01/2017567.33
13ClientAMD567ABBCCDDEE3,146.53USD09/01/2017
14ClientAMD567ABBCCDDEE3,146.53USD09/01/20176293.06
15ClientAMD567ABC16,375.00USD09/01/20176375
16ClientAMD567ABC47,312.50USD09/01/20177312.5
Sheet1
Cell Formulas
RangeFormula
G2=IF(AND(A2=A3,C2=C3),"",SUMIFS(D$2:D2,A$2:A2,A2,C$2:C2,C2))


This could also be applied by code if needed:

Code:
With Range("G2:G" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula = "=IF(AND(A2=A3,C2=C3),"""",SUMIFS(D$2:D2,A$2:A2,A2,C$2:C2,C2))"
    .Value = .Value
End With
 
Last edited:
Upvote 0
First of all thanks for quick reply!

I have already tested and code of FormR is working perfectly.
The code of MickG is not present sum of amounts in the column, but maybe I made a mistake becouse I modified one column.

MickG could you please explain to me your code - I would be really grateful.
 
Upvote 0
I figure it out, is it possible to add something to your code MickG that I will not inserting value if there is nothing to sum?
 
Upvote 0
Can you show an example of the data where the code fails, showing both the correct result and the incorrect result.
At the moment I get the code to fail !!!
 
Upvote 0
Sorry MickG, its working perfectly! I made a mistake when I was changing the column, becouse I have added one. Thanks a lot! you are amazing :-)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top