Sum + Choose + Start + End

[Scipio]

=SUM(CHOOSE('Gross Profit Analysis Tool v.6 No macros (From Chase Tues. PM).xlsx'!Start,Pln_RV!F8,Pln_RV!G8,Pln_RV!H8,Pln_RV!I8,Pln_RV!J8,Pln_RV!K8,Pln_RV!L8,Pln_RV!M8,Pln_RV!N8,Pln_RV!O8,Pln_RV!P8,Pln_RV!Q8):CHOOSE('Gross Profit Analysis Tool v.6 No macros (From Chase Tues. PM).xlsx'!End,Pln_RV!F8,Pln_RV!G8,Pln_RV!H8,Pln_RV!I8,Pln_RV!J8,Pln_RV!K8,Pln_RV!L8,Pln_RV!M8,Pln_RV!N8,Pln_RV!O8,Pln_RV!P8,Pln_RV!Q8))

I'm unsure how to interpret the formula above, particularly the use of the "Start" and "End" parts of the formula.

Can anyone help?

Many thanks.

 

[jasonb75]

Start and end appear to be named ranges in the 'Gross Profit Analysis Tool' workbook, those named ranges will be referring to cells (or formulas) that return 2 numbers between 1 and 12 (I'm going to guess that Pln_RV contains 1 column per month?

The 2 choose functions each return a cell based on the numbers returned from the 2 named ranges, which together will make a range for the sum function to add up.

An alternative formula that you may find a little easier to understand.

=SUM(INDEX(Pln_RV!F8:Q8,'Gross Profit Analysis Tool v.6 No macros (From Chase Tues. PM).xlsx'!Start):INDEX(Pln_RV!F8:Q8,'Gross Profit Analysis Tool v.6 No macros (From Chase Tues. PM).xlsx'!End))

 

[Scipio]

Start and end appear to be named ranges in the 'Gross Profit Analysis Tool' workbook, those named ranges will be referring to cells (or formulas) that return 2 numbers between 1 and 12 (I'm going to guess that Pln_RV contains 1 column per month?

The 2 choose functions each return a cell based on the numbers returned from the 2 named ranges, which together will make a range for the sum function to add up.

An alternative formula that you may find a little easier to understand.

=SUM(INDEX(Pln_RV!F8:Q8,'Gross Profit Analysis Tool v.6 No macros (From Chase Tues. PM).xlsx'!Start):INDEX(Pln_RV!F8:Q8,'Gross Profit Analysis Tool v.6 No macros (From Chase Tues. PM).xlsx'!End))

I see where the named cells are now and understand the formula --- that was the key to understanding. Many thanks Jasonb75.