SUM and CHOOSE formula question

[sea_captain]

I work with P&L's a lot and do a lot of current month/YTD variances to current year / prior year and budget.

I've consolidated a large file from ~50MB to 20MB by reorganizing a mapping table
In this file i have a Flash report for Revenue and EBITDA by divisions / service lines and Acquisitions.
One of these acquisitions uses proforma data from Jan-Oct 2023 with Actuals in Nov and Dec23.

i use "CHOOSE" to pull the Current Month (2024 and 2023 actual and 24 budget) and use "SUM/CHOOSE" for YTD data (reading across 12 months ('24A/'23A/'24B)

I need to show "As Reported" data for one of the acquisitions (less proforma data / Jan-Oct23)
For the Current Month(s), if i use this formula: (A1 = the number of the month, i.e, "8" = Aug / B1 = Jan23, C1=Feb23 etc.)
=CHOOSE($A$1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1,M1) - this will give me the number of the column (month)

Since i need to pull AS REPORTED data, actuals starting in Nov23, i changed the formula to:
=CHOOSE($A$1,0,0,0,0,0,0,0,0,0,0,L1,M1), this will give me only numbers in Nov and Dec (L1 and M1) - this works.

The problem i'm having is the YTD formula, which looks like this
=SUM(B1:CHOOSE($A$1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1,M1))
If i change it to =SUM(0:CHOOSE($A$1,0,0,0,0,0,0,0,0,0,0,L1,M1)) i get a #VALUE error.
I've played with the formula for a good bit with no luck. Anyone know a work around for this?
I can put a note somewhere to remind myself to pull the data come Nov/Dec but would rather automate if possible.

Thanks

 

[Scott Huish]

I don't understand how your second CHOOSE formula could work since it would only pick one of those values not 2, but not sure why you're using CHOOSE in the first place.

If you're trying to sum the values from the first month to M1. Then I would do this:
=SUM(INDEX(B1:M1,A1):M1)

 

[sea_captain]

may be this picture helps. the formula you provided didn't bring back the number i was looking for.
every month i change cell C1 (i.e. from 11 to 12) and that will give December amount and YTD amount. I just need to figure out how to add the YTD formula, but exclude the months between Jan and Oct like it's done for the CM formula.

 

[Fluff]

How about

=SUM(L4:INDEX(B4:M4,C1))

 

[sea_captain]

How about

=SUM(L4:INDEX(B4:M4,C1))

that works if im in month 11 and 12 (YTD rollup), but if i change the month below November / 11, *pic above Aug/8), it gives me a funky number, when it should be ZERO

 

[Fluff]

Ok, how about

=if(c1<11,0,SUM(L4:INDEX(B4:M4,C1)))

 

[sea_captain]

Ok, how about

=if(c1<11,0,SUM(L4:INDEX(B4:M4,C1)))

That works (oct above / dec below)

THANKS!

 

[Fluff]

Glad we could help & thanks for the feedback.