Is there a formula to restrict counting ID once?

Sara Ross

In my sheet, I have a column that counts the number of extensions for each request. I would like my sheet summary to show the number of of requests that have been extended once, twice, three times, etc. The number of requests extended once was easy to formulate since the request is only referenced once (=COUNTIF([Number of Extensions]:[Number of Extensions], 1). However, I'm having problems correctly counting those with ID's that repeat. Is there something I can add to the formula that would only count Request ID's once even though there are multiple rows with that ID?) Would there be a better way to set up?

Thanks!

Genevieve P.

Hi @Sara Ross

What you're looking for is the DISTINCT function! This will count Unique Values in that column, so only counting one ID, even if it's repeated.

You'll want to use COLLECT as well, wrapped in a COUNT function. The COLLECT will work like the IF portion, filtering down based on your other criteria.

Try this:

=COUNT(DISTINCT(COLLECT([Request ID]:[Request ID], [Number of Extensions]:[Number of Extensions], 1)))

Cheers,

Genevieve

Sara Ross

Awesome...worked perfectly!

Much appreciated,

Sara

Genevieve P.

Wonderful! Glad I could help. 🙂

Sara Ross

Worked beautifully!