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.

Find more posts tagged with

Formulas

Cell linking

Comments

Paul Newcome

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

Alejandra

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:

SUMIFS: https://help.smartsheet.com/function/sumifs
YEAR: https://help.smartsheet.com/function/year
AND: https://help.smartsheet.com/function/and
MONTH: https://help.smartsheet.com/function/month

Ami Veltrie

https://community.smartsheet.com/discussion/comment/202401#Comment_202401

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

Ami Veltrie

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

Genevieve P.

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

Paul Newcome

@Ami Veltrie

What @Genevieve P. said.

Ami Veltrie

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

Genevieve P.

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.

Ami Veltrie

@Genevieve P.

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

I'm all set!

Genevieve P.

Wonderful! I'm glad to hear it!

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…