How To create a revenue summary formula
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!
Best Answer

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
Answers

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.

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

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

Hi Paul,
All sorted now thankyou for your help😊

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!