If Month = 12

Options

I am trying to get a formula to count the number of instances that match certain criteria if it was created last month in the current year, however, I don't really know how to add in if last month = 12 then look for last year.


In the formula below [Last Year]1 has a value of 2019 (formula to show whatever last year was)


=COUNTIFS({Nature}, [Primary Column]3, {Branch}, $[SOURCE FROM]$2, {Injury Date}, IF(AND(MONTH(@cell) = 12, YEAR(@cell) = [Last Year]1), 1, 0))

Answers

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    Options

    @Jenna Bailey

    To get last year you can use YEAR(TODAY()) - 1

    Does that help?

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Jenna Bailey
    Jenna Bailey ✭✭✭✭✭
    Options

    even doing that I am still getting an error

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    Options

    @Jenna Bailey

    No need for that IF in the function:

    =COUNTIF(Dealer1, AND(MONTH(@cell) = 12, YEAR(@cell) = YEAR(TODAY()) - 1))

    Cheers,

    Ramzi

    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Jenna Bailey
    Jenna Bailey ✭✭✭✭✭
    Options

    I am actually looking for a countif formula the takes in to account that January is a month and December is the month previous.

    The below is how I have everything set up, i just need to somehow add in a statement that if the month is January to go back and look at December of the previous year


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!