Sum only Unique values based on criteria in excel

Question

Is there a way only unique values are totalled based on a criteria?

For e.g. in this table, if I want to add only unique values at an "ID" level, it should be 510. But the current output is doubling.

Similarly, if I want to add only unique values at a "Concat" level, then it shoud be 350, but currently it is doubling or tripling based on the data.

Answer 1

It should work.

Answer 2

=SUM(CHOOSECOLS(UNIQUE(FILTER($A$4:$G$11,$B$4:$B$11=B4)),6))

=SUM(CHOOSECOLS(UNIQUE(FILTER($A$4:$G$11,$C$4:$C$11=C4)),6))

Answer 3

i tried this on a dataset that has columns from A to AY with 6000 rows, with the values to be summed in column AY,

ID1 in AU, ID2 in AV... and this is the formula applied

=SUM(CHOOSECOLS(UNIQUE(FILTER($AU$2:$AY$6000,$AV$2:$AV$6000=AV2)),5))

Sample columns without data and formula applied on the actual dataset...

Answer 4

it worked! i realized i had an error with the concatenation! thanks a lot..

Answer 5

You are welcome. You may also give feedback on it. Thank you:)