Sum values from Range 1 of cooresponding unique values in range 2

[dlamin130]

Hey guys,

I am trying to sum the values from range 2 of cooresponding unque values in range 1. If you were to sum all the values in range 2 you would get 30. However, I would like to sum the values in the range, but only the first value of their are duplicate values in rage 2. I dont want it to use the values in range 2 from "AA" twice since there are two "AA" values. Any help is greatly appreciated!

values to sum.png

[Ron Coderre]

With your sample data in A1:A10

Please Login or Register  to view this content.

This regular formula returns the sum of Col_B values that correspond to the first instance of the Col_A text

Please Login or Register  to view this content.

In that example, the formula returns: 21
(1+0+2+0+3+4+5+6+0=21)

Is that something you can work with?

[vlady]

@ Ron just for my effort have to post it.

@dlamin this is with a helper column..

asample.xls

[dlamin130]

This is awesome! I really appreciate the help on this.

There is one problem however: If there is a blank cell in the Range 1 column (but there is still a value in Range 2), the entire formula will return a #N/A value. Would you know how to fix it so that it will still count the values in Rage 2 even though there isnt data in range 1?

Additionally, is it possible to sum the values horizontally? I would like to know the total per value in Range 1 but with the same limitation of the unique value. horizontal.png. Thanks again for all your help!

[Ron Coderre]

Any chance you could post a workbook? Click "Go Advanced" and click the "manage attachements" button to get started. (png files are a poor substitute.)

In the meantime...this variation of the formula I posted is durable against blanks

Please Login or Register  to view this content.

[dlamin130]

@ Ron just for my effort have to post it.

@dlamin this is with a helper column..

Attachment 174056

vlady. Thank you so much for the helper column. The fomula in the helper column is EXACTLY what I was looking for. I really appreciate your help on this.

Would you be able to explain to me in layman's terms what this formula actually does? Knowing how the seprate fuctions work together in the forumula would be really helpful for me since it would help me understand in the future how to use it properly. Thank you again so much!

[vlady]

Hello

=IF(COUNTIF($A$2:A2,A2)>1,"",MAX(($A$2:$A$10=A2)*(B2:B10)))

COUNTIF($A$2:A2,A2)>1 - count if there are duplicate >1
as you pull it down formula becomes
COUNTIF($A$2:A3,A3)>1,"" - notice A2:A3 so there is duplicate because the count is greater than 1 then put null/blank ""

MAX(($A$2:$A$10=A2)*(B2:B10))) get the max or the first value from the countif in column B

not really good in explaining.