How do I calculate the forecast income for the next 14 days from today.

Jason Holmes

Hi there,

I'm new to Smartsheet formulas and an struggling to gain the answer I want in order to report it.

I have 1 column with set fields advising expected payment dates and another with expected payment values.

I would like to sum the expected payment values due within the next 14 days to establish a cash flow forecast.

Could anyone help please?

Thanks

Mike Wilday

Oh dang. I also noticed that the formula cut out my less than criteria. This editor in the community does weird things when it starts seeing greater than and less than symbols. Try this one. 😁

=sumifs([Payment Column Name]:[Payment Column Name], [Due Date Column Name]:[Due Date Column Name], >=Today(), [Due Date Column Name]:[Due Date Column Name], <=Today(14))

Mike Wilday

I would us the =SUMIFS formula.

Try something like this.

=sumifs([Payment Column Name]:[Payment Column Name], [Due Date Column Name]:[Due Date Column Name], [Due Date Column Name]:[Due Date Column Name], <=Today(14))

Jason Holmes

Hi Mike,

thanks you for you assistance but the formula didn't work. I have attached a file (in excel)of what I am trying to achieve in Smartsheet.

Jason

data.xlsx

Mike Wilday

Can you copy and paste the formula as you input it in smartsheet? That might give us some insight.

Mike Wilday

Oh dang. I also noticed that the formula cut out my less than criteria. This editor in the community does weird things when it starts seeing greater than and less than symbols. Try this one. 😁

=sumifs([Payment Column Name]:[Payment Column Name], [Due Date Column Name]:[Due Date Column Name], >=Today(), [Due Date Column Name]:[Due Date Column Name], <=Today(14))

Jason Holmes

Thanks Mike,

I appreciate your help with this. Have a great day.

Jason

Mike Wilday

Sure thing! Glad it worked for you!