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

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




"Jan" wrote:

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

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

joel wrote:

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

"Jan" wrote:

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

--

Dave Peterson

Hi Jan

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

--
Regards
Roger Govier

"Jan" wrote in message
...
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)))))))