# Month to Date and Year to Date spending

edited 12/09/19

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.

Tags:

• ✭✭✭✭✭✭

For YTD, you could use something along the lines of

=SUMIFS({Master Sheet Amount Spent}, {Master Sheet Date}, YEAR(@cell) = YEAR(TODAY()))

and for MTD

=SUMIFS({Master Sheet Amount Spent}, {Master Sheet Date}, AND(YEAR(@cell) = YEAR(TODAY()), MONTH(@cell) = MONTH(TODAY())))

• Employee

Hi Bdragun,

Here's a helpful link to reference when creating formulas in Smartsheet: https://help.smartsheet.com/topics/formulas-and-functions

• ✭✭✭✭✭✭

@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())))

• ✭✭✭✭✭✭
edited 10/14/22

@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())))

• Employee

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
• ✭✭✭✭✭✭
edited 10/18/22

@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.

• Employee

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.

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭

No, thank YOU!! Not sure what happened overnight but now the formulas are working using your formula correction.

I'm all set!

• Employee

Wonderful! I'm glad to hear it!