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
-
You forgot the second DATE function.
You can also save yourself from having to edit two separate dates if you want to adjust which year you are summing for by using the method above where you use YEAR(@cell) = 2020 as your criteria.
Answers
-
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
-
@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.
-
You forgot the second DATE function.
You can also save yourself from having to edit two separate dates if you want to adjust which year you are summing for by using the method above where you use YEAR(@cell) = 2020 as your criteria.
-
@Paul Newcome Thank you for catching my error! The formula is now working.
-
Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 458 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives