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

dave619

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!

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)

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)

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!