Change COUNT(DISTINCT(COLLECT formula to Sum a different column

System

This discussion was created from comments split from: Countifs with Distinct?.

Josh S

Does anyone have recommendations to convert the =COUNT(DISTINCT(COLLECT(… formula to Sum a different column?

For example, instead of counting {Email}, sum the Value in {Value}, but still ensuring {Email} is unique?

=COUNT(DISTINCT(COLLECT({Email}, {Geo}, ="AMER")))

Paul Newcome

You would need a helper column on the source sheet that has a column formula adding the total [Value] based on [Email]@row then dividing it by the count of rows that have [Email]@row.

Then you would sum this helper column in the metrics sheet.

Genevieve P.

@Josh S - sending you a tag in case you missed the comment from Paul! 🙂