SUMFIS btwn dates
I am scratching my head because this is a simple formula I have used in the past, but I cannot figure out why this is returning 0 when there are 2 values that should be summed in this period:
=SUMIFS(Amount:Amount, Date:Date, <=DATE(2024, 6, 30), Date:Date, >DATE(2024, 3, 31))
Best Answer
-
If you right click on the Date column and go to "Edit Column Properties", what is the Column Type? Is it Date or is it Text/Number?
Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)
SSFeatures - The browser extension that adds more features into SmartSheet.
- Report PDF generator that supports grouped and summarized reports
- Automatic sorting, sorting with filters, saving sort settings
- Hiding and unhiding columns, and spell checking
Answers
-
Hi @Btrombler ,
I just made a sheet then tested out your formula and it works for me:
Is this the real formula that you use in your sheet, or does your real formula use cross-sheet references or something? If they do use cross-sheet references, make sure that your reference includes the entire column.
Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)
SSFeatures - The browser extension that adds more features into SmartSheet.
- Report PDF generator that supports grouped and summarized reports
- Automatic sorting, sorting with filters, saving sort settings
- Hiding and unhiding columns, and spell checking
-
hi @SSFeatures yes, that is the exact formula. Of note, this is in a sheet summary, not an actual cell.
-
Ah good point. I just tried it as a sheet summary and it looks correct to me.
Can you try recreating the field and see if that fixes the formula? Super weird!
Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)
SSFeatures - The browser extension that adds more features into SmartSheet.
- Report PDF generator that supports grouped and summarized reports
- Automatic sorting, sorting with filters, saving sort settings
- Hiding and unhiding columns, and spell checking
-
@SSFeatures gave that a try, but it didnt seem to help :(
=SUMIFS(Amount:Amount, Date:Date, <=DATE(2024, 6, 30), Date:Date, >DATE(2024, 3, 31))
-
If you put the formula in a cell within the sheet, does it work?
What if you try duplicate the Date column (e.g. Date2), then update the formula to use Date2? Similarly with Amount and "Amount2"?
I'm just curious if something is buggy in your sheet somehow.
Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)
SSFeatures - The browser extension that adds more features into SmartSheet.
- Report PDF generator that supports grouped and summarized reports
- Automatic sorting, sorting with filters, saving sort settings
- Hiding and unhiding columns, and spell checking
-
In the cell w/n the sheet, it still brings up "0"
I tried with both the updated columns and a mix of one and not the other and they all turn up 0 :(
=SUMIFS([Amount2]:[Amount2], [Date2]:[Date2], <=DATE(2024, 6, 30), [Date2]:[Date2], >DATE(2024, 3, 31))
-
just tried creating a WHOLE new sheet and c/p the data over and it's still turning up 0
=SUMIFS(Amount:Amount, Date:Date, <=DATE(2024, 6, 30), Date:Date, >DATE(2024, 3, 31))
-
If you right click on the Date column and go to "Edit Column Properties", what is the Column Type? Is it Date or is it Text/Number?
Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)
SSFeatures - The browser extension that adds more features into SmartSheet.
- Report PDF generator that supports grouped and summarized reports
- Automatic sorting, sorting with filters, saving sort settings
- Hiding and unhiding columns, and spell checking
-
@SSFeatures omg OF COURSE! that did it!! thank you so much. Can't believe i didnt check that piece.
-
Haha no problem! I should have thought of that way earlier too. Took us both a long time for something simple lol. Glad it works now!
Nathan Braun (Founder of SSFeatures) (nathan@ssfeatures.com)
SSFeatures - The browser extension that adds more features into SmartSheet.
- Report PDF generator that supports grouped and summarized reports
- Automatic sorting, sorting with filters, saving sort settings
- Hiding and unhiding columns, and spell checking
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
