Formula Help

James Mezzenga

So I need some help with a forumla, not sure if its possible or not.  New to smart sheets as well so im hoping for some guidance.  I have a Sales Pipeline document that ive created to track all projects in the sales pipline with summary info at the top of the page on how many jobs are in each stage of the process and the $ amounts.  Now where im stuck.  I have a column for expected close date, and a column for qouted price.  What i want to do is on my summary section at the top have a box that says "To close in within 30 days", one for "To close within 60 days", 90 days and 4-6 months.  I want to be able to look at that summary how much income from sales we will can expect to have come in in 30, 60, 90 and 4-6 months to give us a some good color on cashflow.  Im having a hard time getting that formula down.  any suggestions.  Here is a screenshot so you can see what im talking about.  I want it to go off of the qouted price not the probably pipeline income (weighted) column.  

 

Thanks in advanvce!

Shaine Greenwood

Hello James,

 

You might be able to take care of this with a SUMIF formula that compares today's date to your expected close date. You can use the TODAY formula to make this date comparison. I'd suggest checking out our Using Formulas article in the Help Center for more information on this: https://help.smartsheet.com/articles/775363-using-formulas

 

UPDATE: I came up with a formula that might help you. You'll want to change the cell reference range and possibly tweak the formula for your specific use case:

 

=SUMIF(Finish:Finish, TODAY(90) >= @cell, Status:Status)

 

SUMIF formulas work as such:

 

=SUMIF(Criterion Range, Critrion, Sum Range)