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
edited 07/12/17

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

• Employee
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

• Options

Awesome Shaine! Working great!

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

• 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).

• ✭✭✭✭✭✭
Options

Nice catch Shaine.

Craig

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

• ✭✭✭✭✭✭
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.