I am trying the count the number of unique sensors (column 1) that are present by visit duration (column 2). Here is a small portion of the data:
Sensor ID Implant duration
13113 1
13113 1
13113 1
13144 1
13144 1
13144 2
13144 2
13144 2
13144 2
13144 2
14018 1
12184 2
13052 1
13052 1
12155 2
12155 3
12155 3
13069 2
13069 2
13018 1
13018 1
13019 1
13019 1
13049 1
13054 3
13060 3
13108 2
13108 2
So the count for:
Visit 1 should be 6 (13113, 14018, 13052, 13018, 13019, 13049),
Visit 2 should be 5 (13144, 12184, 12155, 13069, 13108), and
Visit 3 should be 3 (12155, 13054, 13060).
I tried DCOUNTA but it doesn't return the count for the first occurrence, just the total number of entries with an implant duration of 1, 2, or 3. So for example it returns 13 for Visit 1, 11 for Visit 2, and 4 for Visit 3.
I have a lot of data that needs to be preserved and counted so I don't want to apply a filter or remove duplicates.
There would appear to be 7 unique Sensors in your sample data (13113, 13144, 14018, 13052, 13018, 13019, 13049) for Visit ID=1, not 6.
=SUMPRODUCT((B2:B29=1)/(COUNTIFS(B2:B29, 1, A2:A29, A2:A29&"")+(B2:B29<>1)))
=SUMPRODUCT((B2:B29=2)/(COUNTIFS(B2:B29, 2, A2:A29, A2:A29&"")+(B2:B29<>2)))
=SUMPRODUCT((B2:B29=3)/(COUNTIFS(B2:B29, 3, A2:A29, A2:A29&"")+(B2:B29<>3)))
It would probably be best to put the Visit ID into a cell and reference the cell in all three places.