SUMIF and year criterion

Please help me calculate the value according to the date of the cell in which the criterion is being checked.

I use:

=SUMIF([Datum dejanskega konca]:[Datum dejanskega konca]; YEAR([Datum dejanskega konca]@row) = 2022; [Skupaj zaračunano]:[Skupaj zaračunano])

and i get the result 0.


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hello @avgustin0

    You will want to use the @cell reference rather than referencing only the single row

    =SUMIF([Datum dejanskega konca]:[Datum dejanskega konca]; YEAR(@cell) = 2022; [Skupaj zaračunano]:[Skupaj zaračunano])

    This says in your range of [Datum dejanskega konca]:[Datum dejanskega konca] where any cell equals 2022, sum the value in your other column

    Does that give you what you need?


  • Smarty
    Smarty ✭✭✭

    @Kelly Moore

    Hi Kelly,

    I am attempting the same type formula you reference above, but something isn't working. I did have ASA Exp Date cells which were blank, did remove these rows but no luck. Can you identify an corrections needed in the formula below?

    =SUMIF([ASA Exp Date]:[ASA Exp Date]; YEAR(@cell) = 2023; [Qty]:[Qty])

    Thank you!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Smarty

    Are you getting an error (what error) or an unexpected value?

    First verification: The values in your Qty column are actual values that could be summed? Also, you are in a region that uses semicolons instead of commas in formulas. If yes to all of this -

    Let's swap to a SUMIFs so we can add more criteria to help filter

    =SUMIFS(Qty:Qty, Qty:Qty; ISNUMBER(@cell); [ASA Exp Date]:[ASA Exp Date]; AND(ISDATE(@cell); YEAR(@cell)=2023))

    Does this make it work for you?


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

    Hi @Smarty

    I am answering here as well for visibility for others.

    I hope you're well and safe!

    To add to Kelly's excellent advice/answer.

    Try something like this.

    =SUMIFS(Qty:Qty, Qty:Qty, ISNUMBER(@cell), [ASA Exp Date]:[ASA Exp Date], AND(ISDATE(@cell), IFERROR(YEAR(@cell), 0) = 2023))

    Did that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!


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

    Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!


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

    W: | E:[email protected] | 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!