Month to Date and Year to Date spending

Bdragun · 08/23/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.

Paul Newcome · 08/28/19

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 · 08/29/19

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 · 10/14/22

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

Genevieve P. · 10/17/22

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 · 10/17/22

@Ami Veltrie

What @Genevieve P. said.

Ami Veltrie · 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.

Genevieve P. · 10/18/22

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 · 10/18/22

@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. · 10/18/22

Wonderful! I'm glad to hear it!

Month to Date and Year to Date spending

Comments

Help Article Resources

Categories