How many IF's can you have in a nested IF function?

[malik641]

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:AD10,AF10:AH10,AJ10:AL10,AN10:AP10),
IF($AI$3="Final",
SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10,AJ10:AL10),
IF($AE$3="Final",
SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10),
IF($AA$3="Final",
SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10),
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?

[Niek Otten]

Indeed the maximum number of nested functions is 7.
I found it hard to understand your formula. Please explain what you're
trying to achieve so maybe we can help.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> 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:AD10,AF10:AH10,AJ10:AL10,AN10:AP10),
> IF($AI$3="Final",
>
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10,AJ10:AL10),
> IF($AE$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10),
> IF($AA$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10),
> 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=379759
>

[JE McGimpsey]

But it is...

Assuming that "Final" doesn't appear in any of the non-checked cells in
Row 3, you could replace the whole thing with

=SUM(IF(MOD(COLUMN(OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)),4)<>3, OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)))

Note: this must be array-entered (CTRL-SHIFT-ENTER, or CMD-RETURN)


In article <[email protected]>,
malik641 <[email protected]>
wrote:

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

[Niek Otten]

Indeed the maximum number of nested functions is 7.
I found it hard to understand your formula. Please explain what you're
trying to achieve so maybe we can help.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> 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:AD10,AF10:AH10,AJ10:AL10,AN10:AP10),
> IF($AI$3="Final",
>
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10,AJ10:AL10),
> IF($AE$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10),
> IF($AA$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10),
> 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=379759
>

[JE McGimpsey]

But it is...

Assuming that "Final" doesn't appear in any of the non-checked cells in
Row 3, you could replace the whole thing with

=SUM(IF(MOD(COLUMN(OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)),4)<>3, OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)))

Note: this must be array-entered (CTRL-SHIFT-ENTER, or CMD-RETURN)


In article <[email protected]>,
malik641 <[email protected]>
wrote:

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

[Niek Otten]

Indeed the maximum number of nested functions is 7.
I found it hard to understand your formula. Please explain what you're
trying to achieve so maybe we can help.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> 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:AD10,AF10:AH10,AJ10:AL10,AN10:AP10),
> IF($AI$3="Final",
>
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10,AJ10:AL10),
> IF($AE$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10),
> IF($AA$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10),
> 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=379759
>

[JE McGimpsey]

But it is...

Assuming that "Final" doesn't appear in any of the non-checked cells in
Row 3, you could replace the whole thing with

=SUM(IF(MOD(COLUMN(OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)),4)<>3, OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)))

Note: this must be array-entered (CTRL-SHIFT-ENTER, or CMD-RETURN)


In article <[email protected]>,
malik641 <[email protected]>
wrote:

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

[Niek Otten]

Indeed the maximum number of nested functions is 7.
I found it hard to understand your formula. Please explain what you're
trying to achieve so maybe we can help.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> 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:AD10,AF10:AH10,AJ10:AL10,AN10:AP10),
> IF($AI$3="Final",
>
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10,AJ10:AL10),
> IF($AE$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10),
> IF($AA$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10),
> 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=379759
>

[JE McGimpsey]

But it is...

Assuming that "Final" doesn't appear in any of the non-checked cells in
Row 3, you could replace the whole thing with

=SUM(IF(MOD(COLUMN(OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)),4)<>3, OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)))

Note: this must be array-entered (CTRL-SHIFT-ENTER, or CMD-RETURN)


In article <[email protected]>,
malik641 <[email protected]>
wrote:

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

[Niek Otten]

Indeed the maximum number of nested functions is 7.
I found it hard to understand your formula. Please explain what you're
trying to achieve so maybe we can help.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> 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:AD10,AF10:AH10,AJ10:AL10,AN10:AP10),
> IF($AI$3="Final",
>
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10,AJ10:AL10),
> IF($AE$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10),
> IF($AA$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10),
> 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=379759
>

[JE McGimpsey]

But it is...

Assuming that "Final" doesn't appear in any of the non-checked cells in
Row 3, you could replace the whole thing with

=SUM(IF(MOD(COLUMN(OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)),4)<>3, OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)))

Note: this must be array-entered (CTRL-SHIFT-ENTER, or CMD-RETURN)


In article <[email protected]>,
malik641 <[email protected]>
wrote:

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

[JE McGimpsey]

But it is...

Assuming that "Final" doesn't appear in any of the non-checked cells in
Row 3, you could replace the whole thing with

=SUM(IF(MOD(COLUMN(OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)),4)<>3, OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)))

Note: this must be array-entered (CTRL-SHIFT-ENTER, or CMD-RETURN)


In article <[email protected]>,
malik641 <[email protected]>
wrote:

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

[Niek Otten]

Indeed the maximum number of nested functions is 7.
I found it hard to understand your formula. Please explain what you're
trying to achieve so maybe we can help.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> 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:AD10,AF10:AH10,AJ10:AL10,AN10:AP10),
> IF($AI$3="Final",
>
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10,AJ10:AL10),
> IF($AE$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10),
> IF($AA$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10),
> 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=379759
>

[JE McGimpsey]

But it is...

Assuming that "Final" doesn't appear in any of the non-checked cells in
Row 3, you could replace the whole thing with

=SUM(IF(MOD(COLUMN(OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)),4)<>3, OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)))

Note: this must be array-entered (CTRL-SHIFT-ENTER, or CMD-RETURN)


In article <[email protected]>,
malik641 <[email protected]>
wrote:

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

[Niek Otten]

Indeed the maximum number of nested functions is 7.
I found it hard to understand your formula. Please explain what you're
trying to achieve so maybe we can help.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> 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:AD10,AF10:AH10,AJ10:AL10,AN10:AP10),
> IF($AI$3="Final",
>
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10,AJ10:AL10),
> IF($AE$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10),
> IF($AA$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10),
> 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=379759
>

[JE McGimpsey]

But it is...

Assuming that "Final" doesn't appear in any of the non-checked cells in
Row 3, you could replace the whole thing with

=SUM(IF(MOD(COLUMN(OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)),4)<>3, OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)))

Note: this must be array-entered (CTRL-SHIFT-ENTER, or CMD-RETURN)


In article <[email protected]>,
malik641 <[email protected]>
wrote:

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

[Niek Otten]

Indeed the maximum number of nested functions is 7.
I found it hard to understand your formula. Please explain what you're
trying to achieve so maybe we can help.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> 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:AD10,AF10:AH10,AJ10:AL10,AN10:AP10),
> IF($AI$3="Final",
>
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10,AJ10:AL10),
> IF($AE$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10),
> IF($AA$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10),
> 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=379759
>

[JE McGimpsey]

But it is...

Assuming that "Final" doesn't appear in any of the non-checked cells in
Row 3, you could replace the whole thing with

=SUM(IF(MOD(COLUMN(OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)),4)<>3, OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)))

Note: this must be array-entered (CTRL-SHIFT-ENTER, or CMD-RETURN)


In article <[email protected]>,
malik641 <[email protected]>
wrote:

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

[Niek Otten]

Indeed the maximum number of nested functions is 7.
I found it hard to understand your formula. Please explain what you're
trying to achieve so maybe we can help.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> 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:AD10,AF10:AH10,AJ10:AL10,AN10:AP10),
> IF($AI$3="Final",
>
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10,AJ10:AL10),
> IF($AE$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10),
> IF($AA$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10),
> 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=379759
>

[Niek Otten]

Indeed the maximum number of nested functions is 7.
I found it hard to understand your formula. Please explain what you're
trying to achieve so maybe we can help.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"malik641" <[email protected]> wrote in
message news:[email protected]...
>
> 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:AD10,AF10:AH10,AJ10:AL10,AN10:AP10),
> IF($AI$3="Final",
>
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10,AJ10:AL10),
> IF($AE$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10,AF10:AH10),
> IF($AA$3="Final",
> SUM(H10:J10,L10:N10,P10:R10,T10:V10,X10:Z10,AB10:AD10),
> 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=379759
>

[JE McGimpsey]

But it is...

Assuming that "Final" doesn't appear in any of the non-checked cells in
Row 3, you could replace the whole thing with

=SUM(IF(MOD(COLUMN(OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)),4)<>3, OFFSET($H$10,,,1,MATCH("Final",
$G$3:$AM$3,0)+2)))

Note: this must be array-entered (CTRL-SHIFT-ENTER, or CMD-RETURN)


In article <[email protected]>,
malik641 <[email protected]>
wrote:

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

[malik641]

I thought I had replied to this thread a WHILE ago stating that I used one of your solutions....guess not.

Thanks everyone for your help, I did solve my problem (this was a WHILE ago) thanks to all of you.