Forum Discussion
How to count duplicates ... sort of ...
Greetings!
Here is part of a table that I am working on:
| RepairID | ModelID | Serial Number | Date Started | Date Tested | ReportedSymptom |
| SPEA-1010 | PT206 | 8FA552 | 10/22/2025 | 10/28/2025 | No Boot - White Screen |
| SPEA-1024 | PT206 | 8FA552 | 10/22/2025 | 11/4/2025 | No Boot - White Screen |
| SPEA-1037 | PT206 | 7C99BD | 10/22/2025 | 11/10/2025 | EGM No Comms |
| SPEA-1038 | PT206 | 715473 | 8/21/2025 | EGM No Comms | |
| SPEA-1039 | PT206 | 715473 | 11/10/2025 | 11/11/2025 | Failed Battery Test |
| SPEA-1056 | PT206 | 7142AE | 10/20/2025 | 11/12/2025 | Black Screen |
| SPEA-1057 | PT206 | 71584F | 10/20/2025 | 11/12/2025 | Black Screen |
| SPEA-1144 | PT206 | 7142AE | 11/24/2025 | 11/24/2025 | No Boot - Black Screen |
I am trying to count the number of repairs based on serial number in this table. But there are some duplicates. However, if you look at the rows with red font, you will see that the serial numbers, though they are the same, were not being repaired for the same reason. So, these need to be counted as 2 separate repairs. If the repairs to the same serial number are for the SAME symptom, we only count it once.
What would be a good approach to creating a function that will count this into a cell on a separate table?
Any assistance would be greatly appreciated.
2 Replies
Apply this formula:
=COUNTA(UNIQUE(TRIMRANGE(CHOOSECOLS(A2:F10, {3,6})))) / 2HTH
Regards,
IlirU
If i correctly understand 7 is the result for the sample dataset.
=SUMPRODUCT(1/COUNTIFS(C2:C9,C2:C9,F2:F9,F2:F9))
