Sum of values excluding rows with duplicate criteria

user_4768829 0
Viewed 316

I'm certain this has a simple solution but I can't find it!

Problem

I need to sum the values in COL B while excluding all but the first instance of the corresponding criteria in COL A, which may contain duplicate values.

Example Data

Record Count
AA 1
BB 2
AA 1
CC 4
DD 7

Unique Record Count: 14

Attempted Solution

I have messing with SUMIF or SUMPRODUCT but haven't been able to work out how to also include the first instance of the corresponding value for COL A. Whatever build of Excel I have doesn't have the function =UNIQUE().

excel
2 Answers

You could use a helper column to get the summable values. E.g., in column C:

=IF(COUNTIF($A$2:$A2,A2)>1,0,B2)

And then do a SUM(C:C)

Example Data

Record Count Countable
AA 1 1
BB 2 2
AA 1 0
CC 4 4
DD 7 7
user_21835470

enter image description here

Formula in D6 is:

=SUMPRODUCT(AVERAGEIF(A2:A6;A2:A6&"";B2:B6)/COUNTIF(A2:A6;A2:A6))

Notice this will work only if all values assigned to a Record are all the same always.

user_91998280
Related