Count IF Unique Values in 2 Cells
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))))
Answers
-
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
-
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.
-
Mike- When I use that formula I get #Incorrect Argument
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!