Current Month Spending Formula

A Rose
A Rose ✭✭✭
edited 02/08/22 in Formulas and Functions

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!

Tags:

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    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!

  • A Rose
    A Rose ✭✭✭
    Answer ✓

    Hi,

    I ended up using 3 summary columns to assist with the formula, using Right and Left functions.


Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    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!

  • A Rose
    A Rose ✭✭✭

    @Jeff Reisman ,

    would this formula affect a month that doesn't have 31 days?

    Thanks

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/08/22

    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!

  • A Rose
    A Rose ✭✭✭

    @Jeff Reisman

    can we add a OR formula that if it's not 31 days, so we don't need additional columns?

    Thank you!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    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!

  • A Rose
    A Rose ✭✭✭
    Answer ✓

    Hi,

    I ended up using 3 summary columns to assist with the formula, using Right and Left functions.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!