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, pleaseVisit the Current Forums.

Formula Help

James Mezzenga
James Mezzenga ✭
edited 12/09/19 inArchived 2017 Posts

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!

Screen Shot 2017-01-11 at 3.34.26 PM.png

Tags:
· Share on Facebook Share on Twitter

Comments

  • Shaine Greenwood
    Shaine Greenwood Employee
    edited 01/12/17

    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)

    · Share on Facebook Share on Twitter
This discussion has been closed.

Categories

Does this work?<\/p>
\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108205,"type":"question","name":"Either checkbox column to contribute to % complete","excerpt":"I am looking for a formula that will reference two checkbox columns to calculate a % complete if either is checked. I'd also like this formula to calculate an average % complete based on the % complete of the child tasks. This was working correctly when I was using only one checkbox column, but I am struggling to…","snippet":"I am looking for a formula that will reference two checkbox columns to calculate a % complete if either is checked. I'd also like this formula to calculate an average % complete…","categoryID":322,"dateInserted":"2023-07-27T17:53:55+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T19:21:53+00:00","insertUserID":164195,"insertUser":{"userID":164195,"name":"Rachel Kelly","url":"https:\/\/community.smartsheet.com\/profile\/Rachel%20Kelly","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-27T19:00:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-28T00:33:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":34,"score":null,"hot":3380967948,"url":"https:\/\/community.smartsheet.com\/discussion\/108205\/either-checkbox-column-to-contribute-to-complete","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108205\/either-checkbox-column-to-contribute-to-complete","format":"Rich","lastPost":{"discussionID":108205,"commentID":387648,"name":"Re: Either checkbox column to contribute to % complete","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387648#Comment_387648","dateInserted":"2023-07-27T19:21:53+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-07-28T00:33:57+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/V4HP6H7XE3I9\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-27T19:01:16+00:00","dateAnswered":"2023-07-27T18:58:57+00:00","acceptedAnswers":[{"commentID":387623,"body":"

Try this:<\/p>

=IF(COUNT(CHILDREN()) = 0, IF(OR(Done@row = 1, [N\/A]@row = 1), 1, 0), AVG(CHILDREN()))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108195,"type":"question","name":"COUNTIF Function Not Working Correctly","excerpt":"Hello, I have a COUNTIF function I am using to count all cells that are 'Blank' in a column. We should have one result, but the formula is showing as zero. Any thoughts on this one? =COUNTIF([Account Dashboard]:[Account Dashboard], \" \")","snippet":"Hello, I have a COUNTIF function I am using to count all cells that are 'Blank' in a column. We should have one result, but the formula is showing as zero. Any thoughts on this…","categoryID":322,"dateInserted":"2023-07-27T16:41:32+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T19:11:08+00:00","insertUserID":164192,"insertUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164192,"lastUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3380962360,"url":"https:\/\/community.smartsheet.com\/discussion\/108195\/countif-function-not-working-correctly","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108195\/countif-function-not-working-correctly","format":"Rich","lastPost":{"discussionID":108195,"commentID":387638,"name":"Re: COUNTIF Function Not Working Correctly","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387638#Comment_387638","dateInserted":"2023-07-27T19:11:08+00:00","insertUserID":164192,"insertUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-27T19:10:56+00:00","dateAnswered":"2023-07-27T16:47:29+00:00","acceptedAnswers":[{"commentID":387588,"body":"

Hi @ewitt19205<\/a> <\/p>

Try removing the space between the quotes. You are counting all cells with 1 space in the cell. Blanks are counted with the quotes together.<\/p>

When counting blanks though, its also good to use a COUNTIFS and compare the blank off another column that always has a value. Like Countifs when the Project name is not blank (<>\"//www.santa-greenland.com/community/discussion/6941/\") and the Account Dashboard is blank (\"//www.santa-greenland.com/community/discussion/6941/\").<\/p>

I hope that helps.<\/p>

Thanks,<\/p>

Matt<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&siteSectionID=0&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending Posts","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending Posts

  • COUNTIF and OR or IF Formula

    I'm looking to use COUNTIF to calculate if something is the right date and then if it has meets one of the following criteria (using references). My current formula looks like this: =COUNTIFS({Despatch Report Date}, =Date8, {Despatch Report Lot Status}, "Q", {Despatch Report WO Status}, <99 I basically want to count if its…

  • Either checkbox column to contribute to % complete

    I am looking for a formula that will reference two checkbox columns to calculate a % complete if either is checked. I'd also like this formula to calculate an average % complete based on the % complete of the child tasks. This was working correctly when I was using only one checkbox column, but I am struggling to…

  • COUNTIF Function Not Working Correctly

    Hello, I have a COUNTIF function I am using to count all cells that are 'Blank' in a column. We should have one result, but the formula is showing as zero. Any thoughts on this one? =COUNTIF([Account Dashboard]:[Account Dashboard], " ")