Distinct and Countifs even possible?

Mae Peterson

I am trying to find the number of induvial emails who submitted a form each week, each month, and each year. I have a form where users add in their own email and anyone who was involved in filling it out. I want to count the number of unique emails listed during different time frames. I tried to use =COUNTIFS([Contact List]:[Contact List], Distinct(@cell)) and that didn't work.

jmyzk_cloudsmart_jp

Hi @Mae Peterson

In such a case, I would create a separate sheet to get the distinct emails using the INDEX(DISTINCT({email range}), row index) formula.

Then, use the COUNTIF function with the email.

In the demo sheet below, I look up another sheet with the following formula;

[Email] =INDEX(DISTINCT({Sample Email and Date Data : Email}), [#]@row)

Smartsheet

Then, I can use the emails to get email statistics based on the date using the following formula.

[ALL] =COUNTIFS({Sample Email and Date Data : Email}, Email@row)
[May] =COUNTIFS({Sample Email and Date Data : Email}, Email@row, {Sample Email and Date Data : Date}, MONTH(@cell) = 5)
[Monday] =COUNTIFS({Sample Email and Date Data : Email}, Email@row, {Sample Email and Date Data : Date}, WEEKDAY(@cell) = 2)

Below is the sample date sheet.

Smartsheet