Cumulative sum that adjusts based on current month

Question

Hi Team, I have 2 columns in the sheet

One is Month and the values are Jan, Feb, Mar,…. Dec

Another column is $ value against each of the month.

All I need is that based on current date/month, the sum value for Previous month and Current Month sums up.

All I need in a separate cell value to just sum up for Current and Previous month value.

So in Mar, it would be Jan+Feb+Mar (10+20+30=60).

I tried the AI tool that shows this formuale but it generates 0

"

=SUMIF([.Month.]:[.Month.], <=MONTH(Today@row ), [Total Planned Revenue]:[Total Planned Revenue])

"

month

Revenue

Jan

10

Feb

20

Mar

30

Apr

40

May

50

Jun

60

Jul

70

Aug

80

Sep

90

Oct

100

Nov

110

Dec

120

Paul Newcome · Answer

Insert a text/number column called "Month Number" and enter 1, 2, 3, etc. in the rows for each of the month numbers.

A stand-alone formula to give you the cumulative based on today's month would be

=SUMIFS(Revenue:Revenue, [Month Number]:[Month Number], @cell <= MONTH(TODAY())

If you wanted it on every row showing your running total:

=SUMIFS(Revenue:Revenue, [Month Number]:[Month Number], @cell <= [Month Number]@row)

And if you wanted that column blank for future months:

=IF([Month Number]@row <= MONTH(TODAY()), SUMIFS(Revenue:Revenue, [Month Number]:[Month Number], @cell <= [Month Number]@row))

Sam_Harwart · Answer

Month

Revenue

Cumulative

Formula

Jan

10

=Revenue1

Feb

20

30

=Cumulative1+Revenue2

Mar

30

60

=Cumulative2+Revenue3

Apr

40

100

etc

May

50

150

etc

Jun

60

210

etc

Jul

70

280

etc

Aug

80

360

etc

Sep

90

450

etc

Oct

100

550

etc

Nov

110

660

etc

Dec

120

780

etc

Unless you only want this to be a single value that is updating as time goes by, you could just have a couple simple addition formulas in an adjacent column.

KHiatusInfinite · Answer

Did you ever get help with this? I can help Gaurav