How to count the number of events in a given month regardless of year.

Omars
Omars ✭✭
edited 07/12/22 in Formulas and Functions

I'm drawing data into a calc sheet from another sheet and I want to determine the number of events we've had in February (for example) over the course of several years. So I'm trying to do a countif formula for the dates but can't seem to find a workable equation. TIA for your help.

· Share on FacebookShare on Twitter

Answers

  • TVang
    TVang ✭✭✭✭✭

    Assuming that the event date is in the column named, Date_Event, your count of events for the month of February would be...

    = COUNTIF( Date_Event:Date_Event, MONTH(@cell) = "2")

    For March, it would be...

    = COUNTIF( Date_Event:Date_Event, MONTH(@cell) = "3")

    ..and so on.

    · Share on FacebookShare on Twitter
  • Hawk23
    Hawk23 ✭✭✭

    Hi @Toufong Vang ,

    I tried this formula but am having difficulty. On my reference sheet, the column name is "Last Day of Employment" and the formatting of the data entered is in Short Date (e.g., 1/01/24, or 3/04/24). On my Metrics Sheet where I am trying to calculate, I am looking to sum the total number of employee resignations per month. I tried the formula but am stuck. Can you provide more guidance for me?


    image.png image.png


    · Share on FacebookShare on Twitter
  • TVang
    TVang ✭✭✭✭✭

    Hi, @Hawk23, use COUNTIFS() because you're evaluating more than one range--{Last Date of Employment} and {Term Type}.

    Assuming that the range [Last Date of Employment]:[Last Date of Employment] is {Last Date of Employment} (and the same for "Term Type"), the formula would be...

    = COUNTIFS({Last Date of Employment}, MONTH(@cell) = 1, {Term Type}, "Resignation")

    image.png

    Hope this helps!

    You'll additional documentation on Smartsheet functions at:

    https://help.smartsheet.com/functions


    · Share on FacebookShare on Twitter
  • Hawk23
    Hawk23 ✭✭✭

    Hi @Toufong Vang thank you, I got a little further (it no longer says unparseable, lol) but it gave me a value of 0 back for all months.

    The formula I used: =COUNTIFS({Last Day of Employment}, MONTH({Last Day of Employment}) = 1, {Term Type}, "Resignation")


    There should be '8' resignations displaying for January

    image.png


    · Share on FacebookShare on Twitter
  • Hawk23
    Hawk23 ✭✭✭

    @Toufong Vang

    I think I found a solution, I added a hidden column into my reference sheet to calc the month based on date, and then used that as the reference in my metrics sheet and it seems to be working.:)

    · Share on FacebookShare on Twitter
  • TVang
    TVang ✭✭✭✭✭
    https://community.smartsheet.com/discussion/comment/425867#Comment_425867

    @Hawk23 , switch the ranges around. Count "Term Type" instead of "Last Date of Employment".


    =COUNTIFS({Term Type}, "Resignation", {Last Date of Employment}, MONTH(@cell) = 1)

    · 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!