Nested IF Statement

  • Thread starter Thread starter Rose Davis
  • Start date Start date
R

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
 
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 *
 
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
 
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 *
 
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 *
 
That should of course had been:

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

LeoH
 
Back
Top