Sumif Match within a Date Range Help
Hi There,
I'm working with this formula:
=SUMIFS({Weekly Sheet - Company Name Range 1}, {Weekly Sheet - Company Name Range 2}, ="Initiative Name", {Weekly Sheet - Company Name Range 3}, >=DATE(2023, 10, 1), {Weekly Sheet - Company Name Range 3}, <=DATE(2023, 10, 31))
This formula pulls from a another sheet and says if it's this initiative name and between these dates sum the time column.
I'm trying to figure out how to enhance this formula to instead of naming the initiative in the formula, instead saying something like if the initiative name in my current sheet @ this row matches the initiative name from the other sheet, and is between these dates, it will sum the time column.
I'd also like to enhance this formula to point to a cell that I can change the date range on that would then change it where ever referenced, instead of having to change the date range each fiscal on every single formula - if that make sense.
Appreciate any assistance in advance!
Answers
-
Figured this out! Please disregard :)
-
You can simplify your formula to use dates in your sheet and use the AND function. When I am creating a metric sheet, instead of creating helper columns all the time, I have started to use the field in my sheet summary area for 1:1 references when I want to create column formulas referencing the same data.
=SUMIFS({Weekly Sheet - Company Name Range 1}, {Weekly Sheet - Company Name Range 2}, [Initiative Name]@row, {Weekly Sheet - Company Name Range 3}, AND(@cell >= [Start Date]#, @cell <= [End Date]#))
The [Field Name]# refers to the name of the field in the sheet summary area.
For your initiative name, you can reference the @row for the column that has the initiative name.
Hope this helps.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!