Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and Sum unique values
Hello,
I am trying to do a vlookup on Group Names and return a sum of the unique values only. I have many Group names all over this sheet and much of this is repeated (It is important for that sheet). However I just need to have a sum of all unique values. For example Column A Column B Group Name Number in Group F8 20 F7 30 F7 5 F8 20 F7 10 F8 5 F7 30 F7 5 F8 15 F8 5 F8 15 The reu;lt would return a total of 40 for F8(20 + 15 + 5) and a total of 45 for F7 (30 + 5 + 10) Thanks chris |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and Sum unique values
=SUM(IF(FREQUENCY(IF(RngA="F8",MATCH(RngB,RngB,0)) ,MATCH(RngB,RngB,0))0,RngB))
ctrl+shift+enter, not just enter "Diggsy" wrote: Hello, I am trying to do a vlookup on Group Names and return a sum of the unique values only. I have many Group names all over this sheet and much of this is repeated (It is important for that sheet). However I just need to have a sum of all unique values. For example Column A Column B Group Name Number in Group F8 20 F7 30 F7 5 F8 20 F7 10 F8 5 F7 30 F7 5 F8 15 F8 5 F8 15 The reu;lt would return a total of 40 for F8(20 + 15 + 5) and a total of 45 for F7 (30 + 5 + 10) Thanks chris |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and Sum unique values
Hi,
Try this =SUM(IF(FREQUENCY(IF(A2:A12=C1,MATCH("~"&B2:B12,B2 :B12&"",0)),ROW(B2:B12 )-ROW(B2)+1),B2:B12)) Where C1 is the group you want to sum This is an array so commit with CTRL+Shift+Enter Mike "Diggsy" wrote: Hello, I am trying to do a vlookup on Group Names and return a sum of the unique values only. I have many Group names all over this sheet and much of this is repeated (It is important for that sheet). However I just need to have a sum of all unique values. For example Column A Column B Group Name Number in Group F8 20 F7 30 F7 5 F8 20 F7 10 F8 5 F7 30 F7 5 F8 15 F8 5 F8 15 The reu;lt would return a total of 40 for F8(20 + 15 + 5) and a total of 45 for F7 (30 + 5 + 10) Thanks chris |
#4
|
|||
|
|||
So this formula works great for what I am trying to do. however I am trying to add an unknown amount of Rows sometime 10 sometimes 1,000. if I set the Look-up Array to the whole column the formula no longer works. the Columns I am looking up and trying to add are vlookup equations which will sometimes have #N/A and sometimes have values.
would I need to set the for example A (tariff) 7321.12.0000 8421.99.0040 7321.12.0000 8211.92.9045 B (boxes) 0.88 1 0.88 #N/A C (weight) 9.9 12 9.9 #N/A D (goal) list of Tariff codes with number of boxes per code and total weight per code in adjacent columns E and F E4 should equal 1.76 F4 should equal 19.8 (when equation is moved over) =SUM(IF(FREQUENCY(IF(A$4:A$8=D4,MATCH("~"&B$4:B$8, B$4:B$8&"",0)),ROW(B$4:B$8)-ROW(B$4)+1),B$4:B$8)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=if(vlookup for non-unique values | Excel Worksheet Functions | |||
Unique values | Excel Worksheet Functions | |||
Unique Values | Excel Worksheet Functions | |||
Count unique values and create list based on these values | Excel Worksheet Functions | |||
create list of unique values from a column with repeated values? | Excel Worksheet Functions |