Count IF Unique Values in 2 Cells

Shayma Gross

I have a sheet where I am logging trainings. I would like to count the the number of times a a trainer has conducted a training. The issue is that there could be multiple entires for 1 date so I need to only count the total number of times a trainer has conducted trainings on unique dates. I tried the below formula but it always returns 1.

=COUNT(DISTINCT(COLLECT({Training Log 19-20 Range 1}, CONTAINS(Trainer2, @cell), {Training Log 19-20 Range 2}, >DATE(2019, 11, 1))))

Mike Raposo

Hi Shayma,

So if I understand correctly, you're looking to count trainings for a given person on "unique" dates. I'm not sure what you mean by when you say "there could be multiple entries for 1 date." If you can share a screenshot of your data that would be helpful.

As such, I drafted this formula that may work for what you're looking for:

=COUNTIFS({Training Log 19-20 Range 1}, CONTAINS("Trainer2", @cell), DISTINCT({Training Log 19-20 Range 2}), >DATE(2019, 11, 1)

Hope this helps! Let me know if you have any questions.

Best,

Mike

Shayma Gross

Annotation 2020-03-03 103807.jpg

Here is what I have. You can see that Erik Birkholm has trained multiple people on the same date. I would like for that to count as one training. Thanks.

Shayma Gross

Mike- When I use that formula I get #Incorrect Argument