# Thread: Using If function together with Index Function

1. 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. 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. 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. 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))

#### Posting Permissions

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