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")))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
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")))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@Paul Newcome Thanks! That worked perfectly.
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!