Unique Count

CarrieClark

Hi -

We have reports that need to go out to a number of donors each year, some donors get multiple reports, while others only get 1. Each gift officer signs a cover letter for each household. So while a donor might appear several times in my sheet, there is only one signature needed.

Is there a formula/count that gives me the distinct count of the number of pieces needed to sign by a gift officer?

Example:

I don't want to display that Mike has to sign 4 letters when 3 of the 4 are going to the same household. Is there anything that can give me a distinct count based on those 2 columns?

Paul Newcome

Happy to help. 👍️

Paul Newcome

You would need to insert a hidden helper column that joins the household and gift office together. From there you would use this formula:

=COUNT(DISTINCT([Helper Column]:[Helper Column]))

CarrieClark

Ok great.

I added the helper column and was able to get the distinct count - but it's not quite getting me what I need. We have 4-5 gift officers, and I need to know how many times they appear per household, and this is giving me a count of just the household:

(in the example below, Mike is our gift officer)

Paul Newcome

My apologies. I missed the part where you needed it by gift officer. Try this:

=COUNT(DISTINCT(COLLECT(Household:Household, [Gift Officer]:[Gift Officer], @cell = [Gift Officer]@row)))

CarrieClark

Holy buckets - that worked!

Seriously - thank you!

Paul Newcome

Happy to help. 👍️