More than 7 arguments in an "IF" formula

marcie

New Member
Joined
Oct 1, 2006
Messages
2
=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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,956
Messages
5,545,177
Members
410,668
Latest member
Gaexel
Top