Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Unique Entries
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Unique Entries
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)) -- HTH, Barb Reinhardt "Graham" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Unique Entries
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) ? -- Biff Microsoft Excel MVP "Graham" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Unique Entries
On Thu, 20 Mar 2008 20:51:00 +0000, Graham
wrote: 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 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Unique Entries
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) "Graham" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Unique Entries
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. Valko wrote: 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) ? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Unique Entries
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:B1 0)) Adjust ranges to suit. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Graham" wrote in message ... 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. Valko wrote: 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) ? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing Unique Entries
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 Graham wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing multi entries | Excel Discussion (Misc queries) | |||
Summing List entries | Excel Discussion (Misc queries) | |||
Summing List Entries 2 | Excel Discussion (Misc queries) | |||
Summing unique values | Excel Worksheet Functions | |||
Summing the last 7 non-blank entries in a row of data | Excel Discussion (Misc queries) |