sumif date is today to end of month

Jason S

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

 

Shaine Greenwood

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

Awesome Shaine! Working great!

 

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

Shaine Greenwood

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

Nice catch Shaine.

Craig

Jason S

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

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