Current Month Spending Formula
Hello Community,
How do I calculate how much was spent this month using the below columns?
[Spending Date] Date Format
[Spedning Amount] USD Format
Something like =SUMIF([Spending Date]:[Spending Date],(MONTH(TODAY())), [Spedning Amount]:[Spedning Amount]
The Issue is that (MONTH(TODAY())) returns the current month in a single digit number like 2 for Feburary, and the date column has the full dates (Default 02/08/2022, mm/dd/yyyy).
Thank you!
Best Answers
-
Instead of trying to pull the Month out the date range, you could build start and end dates inside your formula to compare to the Date field. We'll need to change to SUMIFS, because we'll have multiple criteria. With SUMIFS, the range we want to add up comes first, followed by criteria ranges and criteria.
Try this:
=SUMIFS([Spedning Amount]:[Spedning Amount], [Spending Date]:[Spending Date], >=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 1), [Spending Date]:[Spending Date], <=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 31))
In English: Add up the Spending Amount for rows where the Spending Date is greater than or equal to the first day of the current month, and where the Spending Date is less than or equal to the last day of the current month.
The other way to do this would be to add a helper column that gets the Month from the date field for each row, then compares that to the current month to find which rows to add up. I would say you'd want to do Month and Year, so that you're pulling from this February and not last February too.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi,
I ended up using 3 summary columns to assist with the formula, using Right and Left functions.
Answers
-
Instead of trying to pull the Month out the date range, you could build start and end dates inside your formula to compare to the Date field. We'll need to change to SUMIFS, because we'll have multiple criteria. With SUMIFS, the range we want to add up comes first, followed by criteria ranges and criteria.
Try this:
=SUMIFS([Spedning Amount]:[Spedning Amount], [Spending Date]:[Spending Date], >=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 1), [Spending Date]:[Spending Date], <=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 31))
In English: Add up the Spending Amount for rows where the Spending Date is greater than or equal to the first day of the current month, and where the Spending Date is less than or equal to the last day of the current month.
The other way to do this would be to add a helper column that gets the Month from the date field for each row, then compares that to the current month to find which rows to add up. I would say you'd want to do Month and Year, so that you're pulling from this February and not last February too.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you @Jeff Reisman!
-
-
Well, LOL, here's what happens when you put this in a cell during February:
=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 31)
So, about using that helper column... 😂
In your "Helper" column:
=IFERROR(VALUE(MONTH([Spedning Date]@row) + "" + YEAR([Spedning Date]@row) + ""), "")
For a February 2022 spending date, you'd get a value of 22022. for rows with a blank spending date, you get a blank Helper cell.
Then for your SUMIFS formula:
=IFERROR(SUMIFS([Spedning Amount]:[Spedning Amount], Helper:Helper, VALUE(MONTH(TODAY()) + "" + YEAR(TODAY()) + "")), "")
Add up Spending Amounts where the Helper column matches today's month and year in the format of "monthyear", 22022.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
can we add a OR formula that if it's not 31 days, so we don't need additional columns?
Thank you!
-
An OR won't work there. With an OR, the system would still consider a row dated 3/1/22 through 3/3/22 to be in the month of February. You'd have to get a bit crazy with some nested IFs in there:
=SUMIFS([Spedning Amount]:[Spedning Amount], [Spending Date]:[Spending Date], >=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 1), [Spending Date]:[Spending Date], IF(MONTH(TODAY()) = 2, <=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 28), IF(OR(MONTH(TODAY()) = 4, MONTH(TODAY()) = 6, MONTH(TODAY()) = 9, MONTH(TODAY()) = 11), <=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 30), IF(OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 3, MONTH(TODAY()) = 5, MONTH(TODAY()) = 7, MONTH(TODAY()) = 8, MONTH(TODAY()) = 10, MONTH(TODAY()) = 12), <=DATE(YEAR(TODAY(0)), MONTH(TODAY(0)), 31), ""))))
Give it a shot, maybe it will work! No guarantees, no time left for me to test it today! 😀
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi,
I ended up using 3 summary columns to assist with the formula, using Right and Left functions.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 285 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
