Summing Unique Entries

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
 
B

Barb Reinhardt

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))
 
T

T. Valko

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) ?
 
R

Ron Rosenfeld

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
 
B

Bob Phillips

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)
 
G

Graham

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.
 
T

T. Valko

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)
 
G

Graham

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
 
Top