Trying to Count Unique ID';s that area assigned to specific Consultants

JMartinez73

I have a master sheet that we use to assign consultants to Company, ID's. I am creating a summary sheet and am looking to count how many company ID's are assigned to the Migration consultant ensuring there are no duplicates (Company ID's are the parent to the entire set of Id's).
I have tried various formulas and i cant get the correct output. This is the latest try.
=COUNT(DISTINCT(IF({Updated Accrual File 1082024 Range 7} = "Consultant Name", {Accrual Master File Range 1}, "")))

Jennifer Kurtz

Hello!

I think you could try =COUNT(DISTINCT(COLLECT

The Collect function will allow you to specify the criteria you need. (You'd be "collecting" the company IDs. Your criteria range would be the sheet & column where you have consultant name, and the criteria would be the specific name you're looking for.)

Hope that helps!

JMartinez73

@Jennifer Kurtz , thank you for the suggestion. I tried that and unfortunately it only returned a 1 which is not the accurate result. I have been racking my brain trying to figure out the best function. Thank you so much for the help.

Jennifer Kurtz

@JMartinez73 - Gah - formulas can be so tricky sometimes!

Are you able to share any screenshots of the sheets you're working in? If so, that might help others troubleshoot.

(Aside from that, you could double check your cross-sheet reference ranges if you haven't already. That's often where i find something got wonky.)