SUMIFS to Calculate Values for a Specific Year/Date Range

edited 06/22/23

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.

Tags:

• ✭✭✭✭✭
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!

• 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.

• ✭✭✭✭✭✭

@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

• 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)))