Nested IF Statement

[Rose Davis]

I have this IF statement and would like to know if it could be shortened by
using a different formula? 1,2,3,4 represent quarters of the year
=IF($D$1=1,SUM('Actual 2003'!C7:E7))+IF($D$1=2,SUM('Actual
2003'!F7:H7))+IF($D$1=3,SUM('Actual 2003'!I7:K7))+IF($D$1=4,SUM('Actual
2003'!L7:N7))

Thanks for your support
Rose Davis

 

[A.W.J. Ales]

Rose,
Try
=SUM(INDEX(C7:N7,1,D1):INDEX(C7:N7,D1+3))

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Bernard V Liengme]

Change to
=IF($D$1=1,SUM('Actual 2003'!C7:E7)),IF($D$1=2,SUM('Actual
2003'!F7:H7),IF($D$1=3,SUM('Actual 2003'!I7:K7),IF($D$1=4,SUM('Actual
2003'!L7:N7)))))

or try =CHOOSE(D1, SUM('Actual 2003'!C7:E7),SUM('Actual
2003'!F7:H7),SUM('Actual 2003'!I7:K7),SUM('Actual 2003'!L7:N7) )

Best wishes
Bernard

 

[A.W.J. Ales]

Sorry, that should be :
=SUM(INDEX(C7:N7,1,3*D1-2):INDEX(C7:N7,3*D1))

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[A.W.J. Ales]

And to make it completely correct :
=SUM(INDEX('Actual 2003'!C7:N7,1,3*D1-2):INDEX('Actual 2003'!C7:N7,3*D1))

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Leo Heuser]

Rose

One way:

=SUM(OFFSET($C$7:$E$7,0,($D$1-1)*3))

 

[Leo Heuser]

That should of course had been:

=SUM(OFFSET('Actual 2003'!$C$7:$E$7,0,($D$1-1)*3))

LeoH