Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

sumif date is today to end of month

Options
Jason S
Jason S
edited 07/12/17 in Archived 2017 Posts

Trying to write a formula that doesn't need manual updating that basically sums a total amount for file that have a closing date from today to the end of the month.  Seems like it should be fairly straight forward but I cant get it to work.

The sheet has parent rows but i want to include all rows in all parent rows in the calculation except a value i the parent row itself

After some searching I've tried these formulas below but obviously its wrong.  ideas?

=SUMIF([Closing Date]:[Closing Date], (MONTH(TODAY()) < 12, DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) - 1, DATE(YEAR(TODAY(), 12, 31)))

 

=SUMIF([Closing Date]:[Closing Date], MONTH(@cell) = MONTH(TODAY()))

 

sumif.png

sumif.png

Comments

  • Shaine Greenwood
    Options

    Hi Jason,

    I haven't tested this out with February, but I believe this will work:

    =SUMIFS(number:number, date:date, @cell >= TODAY(), date:date, DAY(@cell) <= 31)

    Change the number:number reference to the name of the column you want to sum, and the date:date to the date column you want to use for your criteria.

    More on SUMIFS in the help center: https://help.smartsheet.com/function/sumifs

    More on @cell here: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

  • Jason S
    Options

    Awesome Shaine! Working great!

     

    =SUMIFS([Loan Amount]:[Loan Amount], [Closing Date]:[Closing Date], @cell >= TODAY(), [Closing Date]:[Closing Date], DAY(@cell) <= 31)

  • Shaine Greenwood
    Shaine Greenwood Employee
    edited 07/13/17
    Options

    Jason—

    Just thought of something. My formula will probably just sum everything that's greater than the current date.

    Try something like this to make sure it only sums if they're between today and the end of the month: 

    =SUMIFS([Loan Amount]:[Loan Amount], [Closing Date]:[Closing Date], @cell >= TODAY(), [Closing Date]:[Closing Date], @cell <= DATE(YEAR(TODAY()), MONTH(TODAY()), 31))

    Also keep in mind that TODAY() will only update to the current date when the sheet is opened and saved. If you look at it via a report and you haven't opened the source sheet that day, you'll see yesterday's SUM (or the last day the source sheet was opened).

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Nice catch Shaine.

    Craig

  • Jason S
    Options

    Hi guys - I cant figure this out but since I put the formula on the sheet each day the Closing Date column is changing all of the closing dates to 7/31.  There are no formulas in the column but we are finding it changing daily all of a sudden.  We've been using this sheet for 2 years and never had this issue before.

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    Options

    Jason,

    This formula should not impact the [Closing Date] column, assuming that is not where it resides (you will get a circular reference error if you do that)

    Something else is going on.

    Did someone else add a formula you don't know about? That's pretty much one of the most obvious ways this could be happening.

    Craig

This discussion has been closed.