Countifs multi select column and year column with contains


I have a multi-select column from which I am able to get a summary formula to count correctly, but every attempt I've made to account for the year from a second column has returned errors of some kind. The Year column has a formula in it as well: =YEAR([Contract Fully Executed Date]@row)

This is the formula that provides the count of rows that have "Production Planning", Production (on-site)" and "Production Virtual" - and now I would like to only return results if the Year column is 2024. I'm open to using the Contract Fully Executed Date column in the formula instead of the Year 'helper' but my attempts at that formula were even worse.

=COUNTIFS([Support Needed]:[Support Needed], OR(CONTAINS("Production Planning", @cell), CONTAINS("Production (on-site)", @cell), CONTAINS("Production Virtual", @cell)))

Thank you for your help - this is my first post!


  • Dylan T
    Dylan T ✭✭✭
    edited 03/14/24

    Hi @kortneymorrison,

    Try adding the following statement into your countifs statement:

    [Date Column]:[Date Column], IFERROR(YEAR(@cell), 0) = Year

    The Year value you're matching to can be hardcoded, a reference so a value in a Sheet Summary field, or somewhere else on the sheet. Here is an example of this statement I use in a countifs formula on one of my metric sheets, where the year is a field on the metric sheet:

    {Submitted MO Date}, IFERROR(YEAR(@cell), 0) = $Year$1

    Hope this works for you!

    ~ Dylan

  • Hi, Dylan - thanks for your help, but I couldn't get that to work. I adjusted my formula slightly and I'm still struggling. Will you have a look:

    =COUNTIFS([Support Needed]:[Support Needed], CONTAINS("Contracting", @cell), (year:year, "2024"))

    I simplified the summary formula which is going to be better in the long run, but I still need to return the number of times the dropdown for Support Needed contains "Contracting" AND the year column is 2024.

    Thanks again


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @kortneymorrison

    I hope you're well and safe!

    Try something like this.

    =COUNTIFS([Support Needed]:[Support Needed], CONTAINS("Contracting", @cell), Year:Year, IFERROR(YEAR(@cell), 0) = "2024")

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic weekend!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!