sum between dates
sum the amount if the dates range is from Jan 1st - Jan 31st.. Any idea?
Best Answer
-
Hey @Samuel Dowdy Jr
Try this
=SUMIFS({MASTER - Invoice Log Sheet Range 1}, {MASTER - Invoice Log Sheet Range 5}, Data@row, {MASTER - Invoice Log Sheet Range 4}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 1, 31)))
Answers
-
Hey @Samuel Dowdy Jr
SUMIFS is the function you need. It has the syntax (range to be summed, range1, criteria1, range2, criteria2, range3, criteria3) and you can have as many or as few criteria as you need, making sure they are always entered in a range-criteria pair. Be sure that that sum-range is a range that contains sumable values.
One way of looking at your criteria is to search for the entire month
=SUMIFS([your sum column]:[your sum column], [your date]:[your date], IFERROR(MONTH(@cell), 0) = 1)
Date functions are notorious for having errors if cells are blank, or text in the field so it not uncommon to see the IFERROR around a date function
To look at SUMIFS between two dates you need to use the DATE function
=SUMIFS([your sum column]:[your sum column], [your date]:[your date], AND(@cell>=DATE(2021,1,1), @cell<=DATE(2021, 1, 31))
These formulas assume you are looking at data within the same sheet as your formula. You will need cross-sheet references if the data is in a different sheet
Here's more info on the formulas I used.
cheers,
Kelly
-
=SUMIFS({MASTER - Invoice Log Sheet Range 1}, {MASTER - Invoice Log Sheet Range 5}, Data4, {MASTER - Invoice Log Sheet Range 4}, AND(>=DATE(2021, 1, 1), <=DATE(2021, 1, 31)))
This what I have that's not working
-
Hey @Samuel Dowdy Jr
Try this
=SUMIFS({MASTER - Invoice Log Sheet Range 1}, {MASTER - Invoice Log Sheet Range 5}, Data@row, {MASTER - Invoice Log Sheet Range 4}, AND(@cell >= DATE(2021, 1, 1), @cell <= DATE(2021, 1, 31)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!