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))))

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.