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
Check out the Formula Handbook template!