More than 7 arguments in an "IF" formula

[marcie]

=IF(D$14=7,EE20,IF(D$14=8,EE20+ET20,IF(D$14=9,EE20+ET20+FI20,IF(D$14=10,EE20+ET20+FI20+FX20,IF(D$14=11,EE20+ET20+FI20+FX20+GM20,IF(D$14=12,EE20+ET20+FI20+FX20+GM20+HB20))))))


This is what I have so far. D14 refers to a fiscal month (the user enters 1 for Feb etc). EE20 would represent Feb sales. I need to enter if's for the whole year-help.

Thanks
Marcie

 

[JohnnyTightlips]

Hi Marcie,

There's some good suggestions at the following link for handling multiple IF's:

http://j-walk.com/ss/excel/usertips/tip080.htm

Hope that can help

 

[marcie]

Thanks so much!!!! Worked!!!!!

 

[Andrew Fergus]

Hi Marcie and welcome to MrExcel!

Here's another way of doing this without using an IF formula. Try the following formula:

=SUMPRODUCT(N(INDIRECT({"EE20","ET20","FI20","FX20","GM20","HB20"})),--({1,2,3,4,5,6}<=D14))

As you add more cell references in the first array (ie after "HB20") then add additional month numbers into the second array (ie after 6) - each array must have the same number of values for this to work.

Andrew