CountifS with Distinct

I need a function to count how many time a territory as unique SIDs in a sheet. I tried the function below but it won't work.
=COUNTIFS({Total URV Visits Range SID}, DISTINCT({Total URV Visits Range SID}), {Total URV Visits BEC}, District@row)
Best Answer
-
Try a COUNT/DISTINCT/COLLECT instead.
=COUNT(DISTINCT(COLLECT({Total URV Visits Range SID}, {Total URV Visits BEC}, @cell = District@row)))
Answers
-
@frob What are you trying to do? Can you post some screens? I don't think you are using DISTINCT correctly.
-
Try a COUNT/DISTINCT/COLLECT instead.
=COUNT(DISTINCT(COLLECT({Total URV Visits Range SID}, {Total URV Visits BEC}, @cell = District@row)))
-
I need to count how many times this sheet has a unique SID for Appalachian for example.
-
Did you try my formula?
-
I did. It says unparsable.
-
Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?
-
This is my summary sheet that I'm trying to pull the counts into so I can make a graph.
-
You entered an extra closing curly bracket after the second cross sheet reference that needs removed.
-
Arg!! It's always something dumb like a bracket or comma. It works now! Thank you!!!
-
Happy to help. 👍️
-
I have a similar question- why the formula below is not working - add unique SERIES value from one column per PMO per BRAND.
In SERIES, there are 114 unique items in either BRAND1 or BRAND2; this is divided between 20 PMOs - we need to know how many per PMO. Each is a column in the main table - this data is on my calculation sheet for reporting.
=IFERROR(COUNTIFS({2025 PMO Tracker V2 |Brand}, "BRAND1", COUNT(DISTINCT(COLLECT({2025 PMO Tracker V2 |Series}, {2025 PMO Tracker V2| PMO}, [Primary Column]@row)))), "error")
thanks
-
@JAAS You wouldn't use the COUNTIFS. Only the COUNT/DISTINCT/COLLECT. The Brand range/criteria set would simply go into the COLLECT function as another range/criteria set there.
-
I resolved it - used the following - thank you. I tried to get too smart and complicated it for no reason
=IFERROR(COUNT(DISTINCT(COLLECT({2025 Tracker V2 |Series}, {2025 Tracker V2| PD}, [Primary Column]@row, {2025 Tracker V2 |Brand}, "Brand1"))), "error")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!