How to count within date range by name of event and name of employee

Hopefully I'm asking this correctly. I need help with a formula to count the # of events an employee managed by event type and within a date range.

Example below:

Date Column Name: Event Scheduled

Employee Column Name: Employee

Employee: John

Event Column Name: Event Type

Event: Birthday

I would like to count how many birthday events John managed in January 2021.

IMPORTANT: The event type may have multiple selections in the cell. This one has "birthday" and "music".

I want this to be counted and included in my birthday formula and also when I do the same for music formula. I've seen formulas where it will not include it in the count when multiple selections are in the cell.

🧐 Thank you!

Find more posts tagged with

Formulas

Accepted answers

Mike TV

@dave619

=COUNTIFS(Employee:Employee, "John", [Event Type]:[Event Type], CONTAINS("Birthday", @cell), [Event Scheduled]:[Event Scheduled], MONTH(@cell) = 1, [Event Scheduled]:[Event Scheduled], YEAR(@cell) = 2021)

=COUNTIFS(Employee:Employee, "John", [Event Type]:[Event Type], CONTAINS("Music", @cell), [Event Scheduled]:[Event Scheduled], MONTH(@cell) = 1, [Event Scheduled]:[Event Scheduled], YEAR(@cell) = 2021)

All comments

Mike TV

@dave619

dave619

https://community.smartsheet.com/discussion/comment/352529#Comment_352529

WOW! That was fast. And it worked! Thanks a lot @Mike TV

One question: I had read somewhere about creating a hidden month column for the numbering of months. Like Jan = 1, Feb = 2, etc. I guess this is not necessary in this case?

And, is your formula better than using the DATE function?

Thanks again Mike. You the man!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.