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
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!