Formula to count revenue that has been won but has not started billing yet

Randy Van Winkle

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

Paul Newcome

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)

Randy Van Winkle

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

Paul Newcome

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.

Randy Van Winkle

OK, this all seems to work great.  Thanks for your support!

Paul Newcome

Happy to have helped.