Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    I have a formula in cell CN1776 on Sheet1. If the value is negative, the formula must only reference Cell CN1733. If however, the value in CN1776 is positive, I want both CN1776 and CN1733 to be added

    I have used the following formula by cannot get it to work

    =IF(SUM(INDEX(Sheet1!A1776:CN1776,0,92))<0,SUM(IND EX(Sheet1!A1733:CN1733,0,92),SUM(INDEX(Sheet1!A177 6:CN1776,0,92)+SUM(INDEX(Sheet1!A1733:CN1733,0,92) ))))

    Your assistance will be must aoppreciated

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Your post will not work. Change all references to CN1776 to CM1776. Once that is fixed I would think it better to just use =If(Sum(A1776:CM1776)<0,Sum(A1173:CM1776),Sum(A177 6:CM1776))

    Regards,

    Tom Duthie

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Tom

    Thanks for the help. I played around with my formula for 10 mins and managed to get it to work. My solution is =IF(SUM(INDEX(Sheet1!A1776:CN1776,0,92))<0,SUM(IND EX(Sheet1!A1733:CN1733,0,92)),SUM(INDEX(Sheet1!A17 76:CN1776,0,92)))

    Unfortunately your formula is not correct

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Is there a point to using INDEX given that you are using fixed column numbers?

    I also don't see that SUM serves a lot of purpose there, unless you have text entries to avoid?

    =SUM(MAX(0,INDEX(Sheet1!A1776:CN1776,0,92)),INDEX( Sheet1!A1733:CN1733,0,92))




    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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