Smartsheet Basics
Smartsheet Basics
Ask questions about the core Smartsheet application: Sheets, Forms, Reports, Dashboards, and more.
Formula to count revenue that has been won but has not started billing yet
I have a formula that counts the revenue if the stage is "Closed Won"
=SUMIF(Stage:Stage, "Closed Won", [Monthly Revenue]:[Monthly Revenue])
Now I need to add another that references the "Go-Live Date" Date and add up what has been won but is less than 30 days past the go live date
Comments
-
You'll need a helper column. We will call it "Difference" for now. In that column you will enter =[Closed Date]@row - [Go Live Date]@row
Drag fill on down.
That'll give you how many days there are between the two dates for each row.
Then your SUMIF would change to:
=SUMIFS([Monthly Revenue]:[Monthly Revenue], Stage:Stage, "Closed Won", Difference:Difference, <30)
-
Maybe I am not fully understanding, but I don't think that is quite right. First, there is not currently a column for Closed Date and that is not relevant to the desired qualifier. A job might be won 2-3 months before it starts. So, we won't see revenue for a given job for the 2-3 months before go live and for the fist 30 days of the job (we get paid after completing one month of service).
-
By "Closed Date" I meant the date that it was marked as "Closed Won".
The other portion, I had misunderstood what you were looking for. My apologies Try this...:
Checkbox Helper column:
=IF(AND(TODAY() >= [Go Live Column Name]@row, TODAY() < [Go Live Column Name]@row + 30), 1)
Then use:
=SUMIFS([Monthly Revenue]:[Monthly Revenue], Status:Status, "Closed Won", [Helper Column Name]:[Helper Column Name], 1)
You can hide the helper column by sticking it all the way to the right of the sheet and "Hide Column". That way it keeps your sheet looking clean.
What this does is it will check the box in the helper column if Today is between the Go Live Date and 30 days after the Go Live Date.
If you want it to include all days before the Go Live Date + 30, simply delete the BOLD AND UNDERLINED portion in the helper column Formula including the parenthesis after the 30. That would take into account ALL dates that are less than Go Live + 30 even if the Go Live Date is still in the future.
The second formula will add your monthly revenue if the status is "Closed Won" and if the helper box is checked (helper box being checked based on your preference of criteria for the date ranges).
I hope this is what you are looking for.
-
OK, this all seems to work great. Thanks for your support!
-
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Trending in Smartsheet Basics
- Hello Team, May be this is intentional, but want to double check. In a sheet, when i open the conversations window, I am able to see tabs for "Row", "Sheet" "All" and "Unread". On the contrary, in a r…
- Can I and how do I set conditional formatting for a Finish Date font to be red if the date is in the past and % Complete is not 100. Is there a way to set multiple conditions for Conditional formattin…
- Hi Smartsheet Community, I have a question regarding Gantt charts in Smartsheet. Currently, my Gantt chart displays a single bar for each project, representing the overall project start and end dates.…
