How To create a revenue summary formula

TaylorCart

How can I create a formula where by I have rows containing different projects, their value and the dates they will start and end and create a summary to show in what months will generate what value (assuming the value is divided equally over however many months the project spans)

i.e. Row 1 is Project X with a value of $3,000, Starting 1st january Ending 31st march would contain months 1,2 & 3 (or Jan,Feb & March)

this would then say 'Project X is a total of $3,000 spanning over jan, feb and march meaning if divided equally, Jan will have $1,000, Feb will have $1,000 and March will have $1,000'

Then when I have multiple rows of projects I can extract into a summary which will tell me the total value of all project involved in January, February and so on.

Thankyou in advance!

Paul Newcome

Sorry. I got ahead of myself there. Here's the finished formula in the helper column:

=((MONTH([End Date]@row) - MONTH([Start Date]@row)) + ((YEAR([End Date]@row) - YEAR([Start Date]@row)) * 12) + 1) * Value@row

Paul Newcome

You would first need a helper column with a formula that outputs the dollar amount for a single month.

=(MONTH([End Date]@row) - MONTH([Start Date]@row)) + ((YEAR([End Date]@row) - YEAR([Start Date]@row)) * 12) + 1

Then you would use a SUMIFS to get the total amount for a single month from every row like so:

=SUMIFS([Helper Column]:[Helper Column], [Start Date]:[Start Date], AND(IFERROR(MONTH(@cell), 0) <= 6, IFERROR(YEAR(@cell), 0) >= 2023), [End Date]:[End Date], AND(IFERROR(MONTH(@cell), 0) >= 6, IFERROR(YEAR(@cell), 0) >= 2023))

The above is for June 2023. Adjust the 6s for the month you want to sum for and the 2023s for the years.

TaylorCart

Hi Paul,

I've tried implementing this formula from above, one thing i'm struggling with is the second part and how this pulls the sum of value from the month, on my testing sample sheet i have the following

The Sum column contains the second formula you provided

https://community.smartsheet.com/discussion/comment/408432#Comment_408432

Paul Newcome

Sorry. I got ahead of myself there. Here's the finished formula in the helper column:

=((MONTH([End Date]@row) - MONTH([Start Date]@row)) + ((YEAR([End Date]@row) - YEAR([Start Date]@row)) * 12) + 1) * Value@row

TaylorCart

https://community.smartsheet.com/discussion/comment/408615#Comment_408615

Hi Paul,

All sorted now thankyou for your help😊

Paul Newcome

Happy to help. 👍️