Counting the occurances of distinct email addresses that are unknown ahead of time...

Astearn

I am trying to build a sheet to track panel scoring for an exam. So assume that the candidates name is James Kirk (kirk@starfleet.org). After 3 panelists have entered their scoring for Kirk@starfleet.org , I want it to send a notification to me. I cant hard code any email addresses because I will have new candidates all the time and won't know their email addresses ahead of time.

TIA

KPH

Hi @Astearn

Would something like this work? The count goes next to the email address so will work on any email address that is entered and each email address will only have a count of 3, once.

It uses a COUNTIF function that counts the occurrence of the email address in the rows from row 1 to current row. You can then set your trigger for when the count changes and is 3.

The formula is

=COUNTIF(Email$1:Email@row, Email@row)

You cannot make it a column formula, but you can drag it down the column.

If you need to use a column formula, you could use this formula:

=COUNTIF(Email:Email, Email@row)

To create a list like this:

We'd then need to figure out an automation that only sends once even though many rows will be at 3 at the same time. We could automatically check a box on the last row per email and using that as a condition in the automation, using an auto number column for row ID and this formula.

=IF([Row ID]@row = MAX(COLLECT([Row ID]:[Row ID], Email:Email, Email@row)), 1)