Sum a revenue column YTD Based on a subscription start date (or month) and the current month.
I have tried a variety of formulas in my sheet summary to show a YTD value of revenue based on a subscription start date and the current month.
Can anyone point me in the right direction of how to make this possible?
I have created a "month" column for the subscription date as well as today's date
=SUMIFS([EA Net Revenue]:[EA Net Revenue], [Month:Month], <=[YTD Month]:[YTD Month])
Comes back unparsable
Best Answer
-
How exactly are you determining that a revenue should be included in your SUMIFS? is it based on just one of the dates, or either of the dates having overlap with the current month?
It looks like you are currently basing it off of the [Subscription Start] date and basically want to sum the revenue column where any row has a start date from the beginning of this year until the current month. If that is the case, this should work for you:
=SUMIFS([EA Net Revenue]:[EA Net Revenue], [Subscription Start]:[Subscription Start], AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), IFERROR(MONTH(@cell), 0)<= MONTH(TODAY())))
Answers
-
Are you able to provide a screenshot for context?
-
Here is what I have built in thus far. Can set-up how ever necessary to always keep a running YTD Revenue each time sheet is opened.
-
How exactly are you determining that a revenue should be included in your SUMIFS? is it based on just one of the dates, or either of the dates having overlap with the current month?
It looks like you are currently basing it off of the [Subscription Start] date and basically want to sum the revenue column where any row has a start date from the beginning of this year until the current month. If that is the case, this should work for you:
=SUMIFS([EA Net Revenue]:[EA Net Revenue], [Subscription Start]:[Subscription Start], AND(IFERROR(YEAR(@cell), 0) = YEAR(TODAY()), IFERROR(MONTH(@cell), 0)<= MONTH(TODAY())))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!