Month to Date and Year to Date spending
I have a sheet which contains date and amount spent, among other things. I want to show a MTD and a YTD metric on a dashboard. I am assuming I need to make a new sheet with a formula pulling data from the original sheet. I would like this to be ongoing as the original sheet is updated and then at the start of a new month/year have the count start over. Basically trying to have my department be able to look at the dashboard to track overall spending. I am a complete newbie to Smartsheet and have no excel experience, so keep it as low level as possible please. I appreciate the help.
Comments
-
Hi Bdragun,
Here's a helpful link to reference when creating formulas in Smartsheet: https://help.smartsheet.com/topics/formulas-and-functions
For more information on the specific functions that Paul suggested, see:
-
@Paul Newcome Hello Paul. I'm trying to use your formula but I'm getting "Invalid Data Type".
=SUMIFS({Rebate Project Intake-TotalRev}, {Rebate Project Intake-PaymentDate}, AND(YEAR(@cell) = YEAR(TODAY()), MONTH(@cell) = MONTH(TODAY())))
-
@Paul Newcome Hello. I added a client ID to the mix which changed the error to "#INCORRECT ARGUMENT SET'.
=SUMIFS({Rebate Project Intake-TotalRev}, {Rebate Project Intake-ClientIDAvi}, ="1796", {Rebate Project Intake-PaymentDate}, AND(YEAR(@cell) = YEAR(TODAY()), MONTH(@cell) = MONTH(TODAY())))
-
Hi @Ami Veltrie
Can you confirm what type of column it is you're looking to SUM? Is it possible that the data stored in that column is being read as text instead of numerical?
It would be helpful to see screen captures of this column: {Rebate Project Intake-TotalRev}
Another thing we can do is wrap IFERRORs around the MONTH function in case it's giving you an error due to blank cells:
=SUMIFS({Rebate Project Intake-TotalRev}, {Rebate Project Intake-ClientIDAvi}, "1796", {Rebate Project Intake-PaymentDate}, AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), IFERROR(MONTH(@cell), 0) = MONTH(TODAY())))
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P. Hi Genevieve! Thanks for your response.
I tried that formula, still not working. Getting this "#INCORRECT ARGUMENT SET"
Here's a screen shot of the column that corresponds with "{Rebate Project Intake-TotalRev}".
Total Rev is a 'text/number' column.
-
Hi @Ami Veltrie
Thank you for this screen capture! Would you be able to post one showing the formula you used as well? For example, here's the exact same formula structure/syntax that I used as a test in my own sheet, which works without error:
If this is still throwing an error, I may suggest checking each of your three {ranges} to ensure there are no errors in those columns. If there's even one cell with an error in anything referenced, that same error will bubble up to your new formula.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
No, thank YOU!! Not sure what happened overnight but now the formulas are working using your formula correction.
I'm all set!
-
Wonderful! I'm glad to hear it!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!