How many nested IF's???
 

could you have in an IF statement?

here's what I got

and I know, its rediculous.....

=IF($AM$3="Final",
SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:A
D10,AF10:AH10,AJ10:AL10,AN10:AP10),
IF($AI$3="Final",
SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:A
D10,AF10:AH10,AJ10:AL10),
IF($AE$3="Final",
SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:A D10,AF10:AH10),
IF($AA$3="Final",
SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:A D10),
IF($W$3="Final",
SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10),
IF($S$3="Final",
SUM(H10:J10,L10:N10,P10:R10,T10:V10),
IF($O$3="Final",
SUM(H10:J10,L10:N10,P10:R10),
IF($K$3="Final",
SUM(H10:J10,L10:N10),
IF($G$3="Final",
SUM(H10:J10),"")))))))))

I tried to make it as readable as possible.

I get an error at the eighth IF statement....but it looked fine to me
so I decided to slowly add all the IF's starting from the end and
working my back to the beginning....after 7 IF statements it give me an
error on the eighth IF.

I could only guess that excel can only handle 7 IF's???? But that
doesn't seem right.

Any suggestions?


--
malik641
------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=379761

If I'm not mistaken, there's a limit of 7 nested IF statements. So try
the following instead...

A1:

=MATCH(2,1/(T(OFFSET(G3,0,{0,4,8,12,16,20,24,28,32}))="Final" ))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

B1:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(H10,0,ROW(INDIRECT(" 1:"&A1))*4-4,1,3)))

...confirmed with just ENTER.

Hope this helps!

malik641 Wrote:
could you have in an IF statement?

here's what I got

and I know, its rediculous.....

=IF($AM$3="Final",
SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:A
D10,AF10:AH10,AJ10:AL10,AN10:AP10),
IF($AI$3="Final",
SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:A
D10,AF10:AH10,AJ10:AL10),
IF($AE$3="Final",
SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:A D10,AF10:AH10),
IF($AA$3="Final",
SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:A D10),
IF($W$3="Final",
SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10),
IF($S$3="Final",
SUM(H10:J10,L10:N10,P10:R10,T10:V10),
IF($O$3="Final",
SUM(H10:J10,L10:N10,P10:R10),
IF($K$3="Final",
SUM(H10:J10,L10:N10),
IF($G$3="Final",
SUM(H10:J10),"")))))))))

I tried to make it as readable as possible.

I get an error at the eighth IF statement....but it looked fine to me
so I decided to slowly add all the IF's starting from the end and
working my back to the beginning....after 7 IF statements it give me an
error on the eighth IF.

I could only guess that excel can only handle 7 IF's???? But that
doesn't seem right.

Any suggestions?


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=379761