How to count up a number of distinct values.

Sharon B

I have a list of what we call "opportunities" (opps), and some are linked to a specific project name (i.e. there are a number of opps for multiple Customers, but all being worked on at the same time under one project). I need to be able to count up how many opportunities we have but also how many distinct projects we have assigned to each "Owner".

I've attached a data file and a subset screenshot below. Opportunity ID is the primary data column. So you can see for example below that the first three unique opportunity ID's are linked by a common project name. So the owner, Louise, has 3 opps, but only one project. Could anyone help me with a formula that enables me to count up how many distinct projects each owner has. I have tried various iterations of using count, count if, distinct, collect and simply cannot get it to work - I only ever get a count of 1 which I know is incorrect. I think my last attempt was something akin to:- =COUNT(DISTINCT(COLLECT({Project Name}, {Sheet Range}, =Owner@row))).

I think part of the issue is that I don't fully understand the logic of the collect function and which range to apply it to, so if anyone could help me walk through the logical steps as well that would be super helpful. Also, is "distinct" the correct thing to use here? It should be such as simple ask but I am stumped so any help very much appreciated - I am a layman so please be gentle with me!! :)

Thanks in anticipation to all you geniuses (or genii?)!

`

Distinct project count by Owner.xlsx

Sharon B

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.

Bassam Khalil

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))),"")

t

Sharon B

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?

Bassam Khalil

@Sharon B

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

Sharon B

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,.

Bassam Khalil

@Sharon B

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")))), "")

Sharon B

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.

Bassam Khalil

@Sharon B

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.

Bassam Khalil

@@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"))), "")

Sharon B

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.

Bassam Khalil

@Sharon B

You are welcome and I will be happy to help you any time.