Struggling with a SUMIFS date range formula
For this use case, I need to sum the total hours (Range 3) for every entry that has a date between January 1, 2021 and January 31, 2021 (Range 1).
The formula I have seems close but is still coming up as Unparseable.
Ideally, what I want is an evergreen formula that can be applied to every calendar month in the year.
Here is my existing formula. Help please! @Paul Newcome tagging you as always as you are the formula guru!
=SUMIFS({Daily Log Tracker - Template Range 1},{Daily Log Tracker - Template Range 1}, >=1012021, [{Daily Log Tracker - Template Range 1},<=13121], {Daily Log Tracker - Template Range 3})
Best Answers
-
There are a couple of issues. The syntax for SUMIFS is opposite that of SUMIF where the range to sum comes first in this one. You will also need to remove the square brackets. And finally dates need to be entered into a formula using a DATE function.
Last but not least I suggest using MONTH and YEAR functions instead of hardcoding dates in. It allows you to just forget about whether that particular month has 28, 29, 30, or 31 days and lets you just specify year and month.
DATE(yyyy, mm, dd)
Assuming Range 1 is the dates and Range 3 is what you want added, give this a try.
=SUMIFS({Daily Log Tracker - Template Range 3}, {Daily Log Tracker - Template Range 1}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021))
-
Hahaha. 🧙
Happy to help. 👍️
You are only missing the "range" for the CONTAINS function. In this case it will be an "@cell" reference.
=COUNTIF({PMO Support Tasks Dept Stakeholders}, CONTAINS(Primary@row, @cell))
Answers
-
There are a couple of issues. The syntax for SUMIFS is opposite that of SUMIF where the range to sum comes first in this one. You will also need to remove the square brackets. And finally dates need to be entered into a formula using a DATE function.
Last but not least I suggest using MONTH and YEAR functions instead of hardcoding dates in. It allows you to just forget about whether that particular month has 28, 29, 30, or 31 days and lets you just specify year and month.
DATE(yyyy, mm, dd)
Assuming Range 1 is the dates and Range 3 is what you want added, give this a try.
=SUMIFS({Daily Log Tracker - Template Range 3}, {Daily Log Tracker - Template Range 1}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021))
-
@Paul Newcome I have concluded that you are magic :) LOL Thank you!
I'm going to throw one more at you since I know you are online right now haha.
I also need a COUNTIF formula for finding each occurrence of a picklist option within a multi-select cell. For example,
What's happening with my formula is that it only counts it if it is the only value in the cell, not if it is at least 1 of the values in the cell. (does that make any sense at all?)
What I currently have is: =COUNTIF({PMO Support Tasks Dept Stakeholders}, CONTAINS(Primary@row))
where the range is a column on another sheet, and I'm looking for the primary value on my calculations sheet.
This is one of many formulas I've tried and is coming up as Incorrect Argument...
-
Hahaha. 🧙
Happy to help. 👍️
You are only missing the "range" for the CONTAINS function. In this case it will be an "@cell" reference.
=COUNTIF({PMO Support Tasks Dept Stakeholders}, CONTAINS(Primary@row, @cell))
-
That was it! Thanks so much!
-
Sure thing! Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!