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-104 in A2. In B2, enter First request about access.

  • Enter R-105 in A3. In B3, enter New request about pickup.

  • Enter R-104 in A4. In B4, enter Same request: added details.

  • Leave A5 empty. In B5, enter Logged contact; request ID not confirmed.

  • Enter R-106 in A6. In B6, enter New 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

  1. 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.

  2. 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.

  3. 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.