distinct formula not working

egrillasca

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?

Paul Newcome

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

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

Melissa Boehl

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.

egrillasca

@Melissa Boehl

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)

Paul Newcome

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

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

egrillasca

@Paul Newcome Thanks! That worked perfectly.

Paul Newcome

Happy to help. 👍️