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

dave619
dave619 ✭✭✭
in Formulas and Functions

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!

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @dave619

    image.png

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

    · Share on FacebookShare on Twitter

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @dave619

    image.png

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

    · Share on FacebookShare on Twitter
  • 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!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!