Sum If, for duplicates?

[mattbnorris]

Morning,

I have a list of monetary values that are associated to reference numbers. What formula can I use in order to sum up the monetary values of the reference numbers if they are the same reference numbers.

For example

Ref: #123 Value: £100
#123 £200
#124 £50
#123 £70
#125 £50
#124 £90

Therefore I want a formula that will add these values based on finding the duplicate reference number in the list.

Ref: #123 Value: £370
#124 £140
#125 £50

Thanks in advance,
Matt

 

[tyija1995]

Hey,

Lets say your references are in cells A1:A6, with values in B1:B6

=SUMIF($A$1:$A$6,"#123",$B$1:$B$6)

Might be easier if instead of "#123" as the second parameter, you put all the unique references in to another column, then reference the column and drag formula down. Like so:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]#123[/TD]
[TD]100[/TD]
[TD][/TD]
[TD]#123[/TD]
[TD]370[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]#123[/TD]
[TD]200[/TD]
[TD][/TD]
[TD]#124[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]#124[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]#125[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]#123[/TD]
[TD]70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]#125[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]#124[/TD]
[TD]90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Where E1 is:
=SUMIF($A$1:$A$6,D1,$B$1:$B$6)

Which can be dragged down.

 

[mohadin]

Hi,
Just in case , If you want try this code

Sub test()
    Dim a As Variant
    Application.ScreenUpdating = False
    a = Range("a1:a" & Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 2)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
'           a(i, 1) = Mid(a(i, 1), 2, 10)   ' It's up to you "#"
            a(i, 2) = Mid(a(i, 2), 2, 10)
            If Not .exists(a(i, 1)) Then
                .Add a(i, 1), a(i, 2)
            Else
                .Item(a(i, 1)) = .Item(a(i, 1)) + a(i, 2) * 1
            End If
            kk = .keys
            itm = .items
        Next
        k = .Count
        Range("d1:d" & .Count) = Application.Transpose(.keys)
        Range("e1:e" & .Count) = Application.Transpose(.items)
    End With
    Application.ScreenUpdating = True
End Sub

 

[salim hasan]

Try this formula

=SUMPRODUCT(($A$1:$A$6=$D1)*($B$1:$B$6))

 

[Peter_SSs]

Welcome to the MrExcel board!

Another option, especially if the original data is not changing, is to use Excel's built-in Pivot Table feature (on the Insert ribbon tab)

Excel Workbook
	A	B	C	D	E
1	Ref:	Value:		Row Labels	Sum of Value:
2	#123	100		#123	370
3	#123	200		#124	140
4	#124	50		#125	50
5	#123	70		Grand Total	560
6	#125	50
7	#124	90
8
PT

 

[mattbnorris]

Thanks for the help everyone!

 

[Peter_SSs]

Thanks for the help everyone!

You're welcome.
You have a few options to choose from.