distinct formula not working
Hello
I'm trying to count how many cases does a subcontractor have in warranty. See, a case can have multiple reclamations and in the source that I'm using, cases are duplicated with different reclamation IDs (waterproofing, electrical, etc).
This is the formula I'm trying to use but it does not work. I've also tried to use ancestor formula but didn't succeed either.
=IF([CASE ID]=DISTINCT(@row), COUNTIF({SUBCONTRACTOR}, ="X SUBCONTRACOTR"),"0")
Any suggestions?
Best Answer
-
You would need a COUNT/DISTINCT/COLLECT combo similar to
=COUNT(DISTINCT(COLLECT({ID Column}, {Team Column}, @cell = "Team A")))
Answers
-
Please share screenshots of both sheets and blackout the data. It is easier to help when we can see exactly what your attempting to accomplish.
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
-
In this sheet for example case 00016 is repeated 4 times, each time with a different warranty claim type, but all claims of a case are attended by the same construction team, in this case Team A. I want to calculate how many cases does a construction team have. I'm trying the formula below but its not working.
=IF([CASE ID]=DISTINCT(@row), COUNTIF({Construction Team}, ="Team A"),"0")
The formula would be on the purple column (Subcontractor=Construction Team)
-
You would need a COUNT/DISTINCT/COLLECT combo similar to
=COUNT(DISTINCT(COLLECT({ID Column}, {Team Column}, @cell = "Team A")))
-
@Paul Newcome Thanks! That worked perfectly.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!