# distinct formula not working

Options
✭✭✭✭

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?

Tags:

• ✭✭✭✭✭✭
Options

You would need a COUNT/DISTINCT/COLLECT combo similar to

=COUNT(DISTINCT(COLLECT({ID Column}, {Team Column}, @cell = "Team A")))

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
edited 03/13/23
Options

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)

• ✭✭✭✭✭✭
Options

You would need a COUNT/DISTINCT/COLLECT combo similar to

=COUNT(DISTINCT(COLLECT({ID Column}, {Team Column}, @cell = "Team A")))

• ✭✭✭✭
Options

@Paul Newcome Thanks! That worked perfectly.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!