vlookup and Sum unique values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Diggsy - 09 May 2008 20:37 GMT

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

Reply to this Message

Teethless mama - 09 May 2008 21:08 GMT

=SUM(IF(FREQUENCY(IF(RngA="F8",MATCH(RngB,RngB,0)),MATCH(RngB,RngB,0))>0,RngB))

ctrl+shift+enter, not just enter

> Hello,
> I am trying to do a vlookup on Group Names and return a sum of the unique
[quoted text clipped - 25 lines]
>
> chris

Reply to this Message

Mike H - 09 May 2008 21:09 GMT

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

> Hello,
> I am trying to do a vlookup on Group Names and return a sum of the unique
[quoted text clipped - 25 lines]
>
> chris

Reply to this Message