How to count up a number of distinct values.

Thank you so much for your help with this. That seems to work fine now. I would never have got to that version of a formula without your help. Really appreciate it.

All comments

Hi @Sharon B

Hope you are fine, i tried to filter your excel file for "Louise" and this is what i find:

so "Louise" have 5 deferent projects with 4 deferent Opportunity so if you want to count the number of distinct values for "Louise" using 1 criteria [Project Name] then please try the following formula:

=IFERROR(COUNT(DISTINCT(COLLECT([Project Name]:[Project Name], Owner:Owner, Owner@row))),"")

Hi @Bassam Khalil,

Thank you so much - I think that does work but need to test and tweek the column names slightly on the real data set and just do a count to make sure (I changed a lot of stuff on example so was anonymous data). Meanwhile, one more thing I have realised I need to do as well is exclude any rows with an Opportunity Stage of "Closed/Won" or "Closed/Lost". Pushing my luck here I know, but could you share how I could exclude these options as well within that formula?

if you like me to fix the formula directly on your sheet please share me as an admin on a sample copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.

My Email for sharing : Bassam.k@mobilproject.it

Thanks so much. I have shared the sheets with you. The sheet I am doing the formulas in is the Buyer metrics sheet. Project column is where the formula you shared is sat now. I have shared a copy of the Source data sheet ICR Workplan also. The "Owner" equivalent column is called "Procurement Team Member". So I am trying to establish how many distinct Project Names a Procurement Team Member is working on that do not have an Opportunity Stage of "Closed/Won" or "Closed/Lost". Hope that makes sense? Thanks so much again for your help,.

I fix it for you, please try the following formula:

=IFERROR(COUNT(DISTINCT(COLLECT({Project Name}, {IC&R Workplan - Buyer},
[Procurement Team Member]@row, {Opportunity Stage}, OR(@cell <> "Closed/Won",
@cell <> "Closed/Lost")))), "")

Hi, the first part of the formula you did works perfectly, but the second part, where I want it to count everything that is not closed is not working. It is still bringing back the same value. i comments on the heet chat we were on to explain what values should be that are returned. First line there should only be 8 projects shown that are not closed and have distinct name. Second should only have 5. the values being returned are for everything including closed opps.

Am out of the office now, please give one sample output value manually so I can test what is the problem. Because I used the filter to check my formula on your shared sheet and the results was OK. So maybe I didn't understand your criteria.

@@Sharon B

I fix it as per your "Closed/Won" & "Closed/Lost" Criteria, please check it. the following is your New formula:

=IFERROR(COUNT(DISTINCT(COLLECT({Project Name}, {IC&R Workplan - Buyer},
[Procurement Team Member]@row, {Opportunity Stage}, @cell <> "Closed/Won",
{Opportunity Stage}, @cell <> "Closed/Lost"))), "")

Thank you so much for your help with this. That seems to work fine now. I would never have got to that version of a formula without your help. Really appreciate it.