Google Sheets
How to Count Unique Request IDs in Google Sheets Without Deleting Rows
Published by OneCell
Use COUNTUNIQUEIFS to count each confirmed request ID once in Google Sheets. Keep each real interaction on its own row, including follow-ups. Count logged interactions and missing IDs separately so you can see what the distinct count covers. Google’s COUNTUNIQUEIFS reference
Set up a log with a repeat and a missing ID
On a tab called Requests, enter Request ID in A1 and Interaction note in B1. Use one stable ID per request and reuse it whenever you log a follow-up. A person can make more than one request. Enter these fictional records as plain text.
Enter
R-104in A2. In B2, enterFirst request about access.Enter
R-105in A3. In B3, enterNew request about pickup.Enter
R-104in A4. In B4, enterSame request: added details.Leave A5 empty. In B5, enter
Logged contact; request ID not confirmed.Enter
R-106in A6. In B6, enterNew request about dates.Leave A7 and B7 empty.
Count known requests and logged interactions
Enter Known request IDs in D1. Put this formula in E1.
=COUNTUNIQUEIFS(A2:A7, A2:A7, "<>", B2:B7, "<>")
This counts distinct IDs from rows with both an ID and an interaction note. The two notes for R-104 contribute one ID. The result is 3. COUNTUNIQUEIFS syntax and range rules
Enter Logged interactions in D2 and put =COUNTA(B2:B7) in E2. Expect 5. Each row with a note contributes one interaction, including the contact with an unconfirmed ID. Keep unused note cells truly empty. COUNTA also counts whitespace and empty strings returned by formulas. Google’s COUNTA reference
Enter Interactions without an ID in D3. Put this formula in E3.
=COUNTIFS(B2:B7, "<>", A2:A7, "")
This counts rows with a note and an empty ID cell. Expect 1 for row 5. Leave an ID cell empty until you can confirm which request the interaction belongs to. Google’s COUNTIFS reference
Check what happens when the ID becomes known
Suppose a record confirms that row 5 belongs to R-105. Enter R-105 in A5. E1 stays at 3, E2 stays at 5 and E3 drops to 0.
Try the alternative where the contact is for a newly confirmed request. Replace A5 with R-107. E1 rises to 4, while E2 and E3 stay at 5 and 0.
Clear A2:A7 in the practice log and keep the notes. The three results become 0 known IDs, 5 interactions and 5 without an ID. Clear B2:B7 as well and all three become zero. Restore the example entries to repeat the checks.
Use consistent IDs and intentional notes in your own log. Extend every formula range to the same last row when the log grows. Read the known-ID count alongside the missing-ID count whenever you report how many requests appear in the log.