I need to have Sum of values in [C] against DISTINCT values of corresponding column [B] in the filter i.e. A

I tried SUM(UNIQUE(FILTER(C:C, (B:B)*(A:A=E2))) also tried SUM(IF(Frequency)))

ending up getting 2900 instead of 3900 for USA.

CodePudding user response：

Use

=SUM(INDEX(UNIQUE(FILTER(A:C,A:A=E2)),0,3))

The idea is that you want to return all three columns to the Unique, If you only return C it will remove the duplicate numbers even if they are attached to different cities. Then use INDEX to return the 3 column. One can also use CHOOSECOLS, but that is more typing.

=SUM(CHOOSECOLS(UNIQUE(FILTER(A:C,A:A=E2)),3))