Sum using more than 6 nested IF functions

[Jan]

I'm looking for an alternative to the IF function detailed below based on 12
nested formulas ie January - December so that depending on which month is
specified in the "input date B1" the year to date column returns the sum of a
differing number of columns....

If the input date is month 1 then the calculation returns the value in
column Q24
If the input date is month 2 then the calculation returns the value in
column P24:Q24
If the input date is month 3 then the calculation returns the value in
column O24:Q24 and so on....

Any ideas?

=IF('Input Date'!$B$1=1,SUM(Q24),IF('Input
Date'!$B$1=2,SUM(P24:Q24),IF('Input Date'!$B$1=3,SUM(O24:Q24),IF('Input
Date'!$B$1=4,SUM(N24:Q24),IF('Input Date'!$B$1=5,SUM(M24:Q24),IF('Input
Date'!$B$1=6,SUM(L24:Q24),IF('Input Date'!$B$1=7,SUM(K24:Q24),0)))))))

 

[joel]

You are limited to 256 character in a formula you can use this

=choose('Input
Date'!$B$1,SUM(Q24),SUM(P24:Q24),SUM(O24:Q24),SUM(N24:Q24),SUM(M24:Q24),SUM(L24:Q24),SUM(K24:Q24))

=Sum(Offset(K24,0,8-'Input Date'!$B$1,1,'Input Date'!$B$1))

which say to start at K24 and move right (8 - 'Input Date'!$B$1) columns and
then select 'Input Date'!$B$1 columns


Now if you are real smart

 

[Roger Govier]

Hi Jan

One way
=SUM(INDEX(F1:Q1,,13-'Input Date'!$B$1):$Q1)

 

[Dave Peterson]

The limit is 1024 characters when measured in R1C1 reference style.