+ Reply to Thread
Results 1 to 22 of 22

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

  1. #1
    Forum Contributor
    Join Date
    06-10-2005
    Location
    Central Jersey
    Posts
    117

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

    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?

  2. #2
    Niek Otten
    Guest

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

    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" <malik641.1qqjim_1118952335.8871@excelforum-nospam.com> wrote in
    message news:malik641.1qqjim_1118952335.8871@excelforum-nospam.com...
    >
    > 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
    >




  3. #3
    JE McGimpsey
    Guest

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

    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 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>,
    malik641 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>
    wrote:

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


  4. #4
    Niek Otten
    Guest

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

    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" <malik641.1qqjim_1118952335.8871@excelforum-nospam.com> wrote in
    message news:malik641.1qqjim_1118952335.8871@excelforum-nospam.com...
    >
    > 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
    >




  5. #5
    JE McGimpsey
    Guest

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

    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 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>,
    malik641 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>
    wrote:

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


  6. #6
    Niek Otten
    Guest

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

    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" <malik641.1qqjim_1118952335.8871@excelforum-nospam.com> wrote in
    message news:malik641.1qqjim_1118952335.8871@excelforum-nospam.com...
    >
    > 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
    >




  7. #7
    JE McGimpsey
    Guest

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

    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 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>,
    malik641 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>
    wrote:

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


  8. #8
    Niek Otten
    Guest

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

    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" <malik641.1qqjim_1118952335.8871@excelforum-nospam.com> wrote in
    message news:malik641.1qqjim_1118952335.8871@excelforum-nospam.com...
    >
    > 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
    >




  9. #9
    JE McGimpsey
    Guest

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

    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 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>,
    malik641 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>
    wrote:

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


  10. #10
    Niek Otten
    Guest

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

    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" <malik641.1qqjim_1118952335.8871@excelforum-nospam.com> wrote in
    message news:malik641.1qqjim_1118952335.8871@excelforum-nospam.com...
    >
    > 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
    >




  11. #11
    JE McGimpsey
    Guest

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

    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 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>,
    malik641 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>
    wrote:

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


  12. #12
    Niek Otten
    Guest

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

    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" <malik641.1qqjim_1118952335.8871@excelforum-nospam.com> wrote in
    message news:malik641.1qqjim_1118952335.8871@excelforum-nospam.com...
    >
    > 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
    >




  13. #13
    JE McGimpsey
    Guest

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

    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 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>,
    malik641 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>
    wrote:

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


  14. #14
    Niek Otten
    Guest

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

    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" <malik641.1qqjim_1118952335.8871@excelforum-nospam.com> wrote in
    message news:malik641.1qqjim_1118952335.8871@excelforum-nospam.com...
    >
    > 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
    >




  15. #15
    JE McGimpsey
    Guest

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

    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 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>,
    malik641 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>
    wrote:

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


  16. #16
    Niek Otten
    Guest

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

    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" <malik641.1qqjim_1118952335.8871@excelforum-nospam.com> wrote in
    message news:malik641.1qqjim_1118952335.8871@excelforum-nospam.com...
    >
    > 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
    >




  17. #17
    JE McGimpsey
    Guest

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

    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 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>,
    malik641 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>
    wrote:

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


  18. #18
    Niek Otten
    Guest

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

    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" <malik641.1qqjim_1118952335.8871@excelforum-nospam.com> wrote in
    message news:malik641.1qqjim_1118952335.8871@excelforum-nospam.com...
    >
    > 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
    >




  19. #19
    JE McGimpsey
    Guest

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

    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 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>,
    malik641 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>
    wrote:

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


  20. #20
    Niek Otten
    Guest

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

    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" <malik641.1qqjim_1118952335.8871@excelforum-nospam.com> wrote in
    message news:malik641.1qqjim_1118952335.8871@excelforum-nospam.com...
    >
    > 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
    >




  21. #21
    JE McGimpsey
    Guest

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

    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 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>,
    malik641 <malik641.1qqjim_1118952335.8871@excelforum-nospam.com>
    wrote:

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


  22. #22
    Forum Contributor
    Join Date
    06-10-2005
    Location
    Central Jersey
    Posts
    117
    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.
    -Joseph

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1