SUM FORMULA PROBLEM EXCEL 1

[elfa]

Hello,

cell a1 will contain a number from 1 to 12. This is determined by the month ie January will be 1. Feb 2 etc.

the sum calculation required depends on the number in a1

I wrote the formula below but I can only get to 7 not to 12. Is there another formula I can use?

=IF(Charts!C82=1,Forecast!C53,IF(Charts!C82=2,SUM(Forecast!C53:Forecast!D53),IF(Charts!C82=3,SUM(Forecast!C53:Forecast!E53),IF(Charts!C82=4,SUM(Forecast!C53:Forecast!F53),IF(Charts!C82=5,SUM(Forecast!C53:Forecast!G53),IF(Charts!C82=6,SUM(Forecast!C53:Forecast!H53),IF(Charts!C82=7,SUM(Forecast!C53:Forecast!I53))))))))

 

[sfvb]

I think this will work:

=IF(AND(C82>0, C82<13), SUM(Forecast!C53:Forecast!OFFSET(C53,0,C82-1)),"")

As an example:
If C82 contained the value 4, then
OFFSET(C53,0,C82-1) would evaluate to
OFFSET(C53,0,4-1) which would evaluate to
OFFSET(C53,0,3)

OFFSET(C53,0,3) evaluates to (C53 + 3 columns) or:
F53

So....
=IF(AND(C82>0, C82<13), SUM(Forecast!C53:Forecast!OFFSET(C53,0,C82-1)),"")
evaluates to
=IF(AND(C82>0, C82<13), SUM(Forecast!C53:Forecast!F53),"")

Steve

 

[elfa]

Thanks very much Steve - worked a treat.

I would never have got there.

Regards
Elfa.