Summing Unique Entries

  • Thread starter Thread starter Graham
  • Start date Start date
G

Graham

I have two columns of over 400 entries in the general format below

NJ/64522/60377 14.5
NJ/64662/60060 7.64
NJ/64907/60388 19.98
NJ/64907/60388 19.98
NJ/61556/59022 2.79
NJ/61641/58422 10.76
NJ/61751/58457 8.03
NJ/61856/58771 11.41
NJ/61856/58771 11.41
NJ/61900/57968 14.93
NJ/61914/59108 19.68
NJ/62038/58548 14.36
NJ/62048/58278 1.38
NJ/62086/58070 2.95
NJ/62158/59052 2.55
NJ/62211/58854 1.17

There are duplicate entries in column A which have the same values in
column B. What I want to do is get the total of all these entries for
only the unique values which would be 132.13 in this case. I know I can
do a unique advanced filter on them and then use the Subtotal function
to give me the figure I am after. Hovwever for what I am doing it would
be really useful if this exercise could be condensed into one function
in a cell. I would value any guidance

Kind Regards
Graham
Turriff
Scotland
 
I can do this by adding a couple of helper columns.

C2: =A2 & " " & B2 -Copy down as needed
D2: =COUNTIF(C$2:C$400,C2) -Adjust cell addresses and copy as needed

In Say Column E, put this

E2: =SUMPRODUCT(--(D2:D400=1),(B2:B400))
 
Is it possible to have duplicates in column A with unique values in column
B?

NJ/64907/60388 19.98
NJ/64907/60388 19.98
NJ/64907/60388 12.25

And in this case you'd only want to sum 19.98 and 12.25 (32.23) ?
 
I have two columns of over 400 entries in the general format below

NJ/64522/60377 14.5
NJ/64662/60060 7.64
NJ/64907/60388 19.98
NJ/64907/60388 19.98
NJ/61556/59022 2.79
NJ/61641/58422 10.76
NJ/61751/58457 8.03
NJ/61856/58771 11.41
NJ/61856/58771 11.41
NJ/61900/57968 14.93
NJ/61914/59108 19.68
NJ/62038/58548 14.36
NJ/62048/58278 1.38
NJ/62086/58070 2.95
NJ/62158/59052 2.55
NJ/62211/58854 1.17

There are duplicate entries in column A which have the same values in
column B. What I want to do is get the total of all these entries for
only the unique values which would be 132.13 in this case. I know I can
do a unique advanced filter on them and then use the Subtotal function
to give me the figure I am after. Hovwever for what I am doing it would
be really useful if this exercise could be condensed into one function
in a cell. I would value any guidance

Kind Regards
Graham
Turriff
Scotland
Click to expand...

This can be done with a UDF.

In writing this UDF, I only checked for unique entries in Column A -- I did not
check to see if the duplicate entries in Column A ALSO had identical entries in
Column B. Is that also a requirement?

In any event, to enter the UDF, <alt-F11> opens the VBEditor. Ensure your
project is highlighted in the project explorer window, then Insert/Module and
paste the code below into the window that opens.

To use this, enter a formula: =SumUniques(rg) where rg is your data range --
only the first column will be checked for duplicates.

It does give a result of 132.13 on your sample data.

=============================================
Option Explicit
Function SumUniques(rg As Range) As Double
Dim c As Range
Dim coll As Collection
Dim i As Long
Dim dTemp As Double

Set rg = rg.Resize(rg.Rows.Count, 1)

Set coll = New Collection
On Error Resume Next
For Each c In rg
coll.Add c.Value, c.Value
Next c
On Error GoTo 0

For i = 1 To coll.Count
dTemp = dTemp + Application.WorksheetFunction.VLookup(coll(i), _
Range(rg, rg.Offset(0, 1)), 2, False)
Next i
SumUniques = dTemp
End Function
===============================
--ron
 
If the dupes are adjacent as per your example,

=B1+SUMPRODUCT(--(A1:A15<>A2:A16),B2:B16)

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
The value in column B always matches with the value in column A, eg
NJ?64907/60388 in column A will always have the same value 19.98, this
value will not change in column B, the pair are inique.
 
Assuming there are no empty cells within the range in column A, try this
array formula:

=SUM(IF(FREQUENCY(IF(MATCH(A1:A10,A1:A10,0)=ROW(A1:A10)-MIN(ROW(A1:A10))+1,ROW(A1:A10)),ROW(A1:A10)),B1:B10))

Adjust ranges to suit.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
All three responses work perfecly and suit the situation. Many thanks
for all your help and the extremely fast response. I am indebted to you all.

Kind Regards,
Graham
 
Back
Top