SUMIFS to Calculate Values for a Specific Year/Date Range


I am trying to use SUMIFS with multiple criteria, and I am failing horribly. I seem to be having trouble when I try to add the criterion to sum data from a specific year (from a Date field) and/or when I attempt to add data from a date range.

I have searched the community questions, and I haven't been successful in finding a solution. It is possible I haven't arrived at the correct post.

Thanks in advance for your assistance!

Best Answer


  • Jennifer WhitlockJennifer Whitlock ✭✭✭✭✭
    edited 08/03/20

    Hi Andrew,

    Without knowing what other info you need in your formula, you should be able to pull the info relative to a specific year by inserting this into your SUMIFS statement as the range and criteria:

    (Date:Date, >DATE(2020, 1, 1) < DATE(2020, 12, 31)

    Would you share any existing progress in your formula to see if anyone can be more helpful?

    Have a good one!

  • Andrew BrimerAndrew Brimer ✭✭✭✭✭

    Hi Jennifer,

    Thank you for your input. I was able to get a bit closer to the solution, but I'm having difficulty now with the cross sheet formula and adding in an IFERROR function when the Date column is not populated or has "TBD" or "Other."

    Thanks again for your quick response.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Jennifer Amador Your criteria section will not work within Smartsheet. You would need to either use an AND statement with @cell references

    Date:Date, AND(@cell > DATE(2020, 1, 1), @cell < DATE(2020, 12, 31))

    or you would need to repeat the range.

    Date:Date, > DATE(2020, 1, 1), Date:Date, < DATE(2020, 12, 31)

    @Andrew Brimer Try this for your criteria...

    IFERROR(YEAR(@cell), 0) = 2020

  • Andrew BrimerAndrew Brimer ✭✭✭✭✭

    @Paul Newcome and @Jennifer Amador

    Here's my current formula which is #UNPARSEABLE:

    =SUMIFS({Data Range to Sum}, {Data_Check Column}, =0, {Data_A Exp Date}, AND(@cell>=DATE(2020, 1, 1), @cell<=(2020,12, 31)))

    Thank you for your assistance.

  • Andrew BrimerAndrew Brimer ✭✭✭✭✭

    @Paul Newcome Thank you for catching my error! The formula is now working.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Happy to help. 👍️

Sign In or Register to comment.