Sum a revenue column YTD Based on a subscription start date (or month) and the current month.

Options
✭✭✭✭

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Are you able to provide a screenshot for context?

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!