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

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

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

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