+ Reply to Thread
Results 1 to 17 of 17

conditional sum (search for first cell containing a value)

  1. #1
    Registered User
    Join Date
    11-06-2014
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    10

    conditional sum (search for first cell containing a value)

    Hello everyone

    I have a row of 5 cells (A1, B1, C1, D1, E1)

    I want to put a formula into F1 that will subtract A1 from E1

    BUT if A1 is empty I want to subtract B1... and if B1 empty I want to subtract C1... and so on...


    Basically I need a formula that will look left to right for the first cell containing a value and subtract that value from E1.

    Do I need some kind of nested if function?? Please help.
    Many thanks

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: conditional sum (search for first cell containing a value)

    Like this?
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: conditional sum (search for first cell containing a value)

    Try

    =E1-INDEX(A1:D1,MATCH(1,INDEX(--(A1:D1>0),1,0),0))

  4. #4
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: conditional sum (search for first cell containing a value)

    I believe this formula does it:

    =IF(A1>0;E1-A1;IF(B1>0;E1-B1;IF(C1>0;E1-C1;IF(D1>0;E1-D1))))

  5. #5
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: conditional sum (search for first cell containing a value)

    Quote Originally Posted by bmouse View Post
    I believe this formula does it:

    =IF(A1>0;E1-A1;IF(B1>0;E1-B1;IF(C1>0;E1-C1;IF(D1>0;E1-D1))))
    You could simplify that to

    =E1-IF(A1>0,A1,IF(B1>0,B1,IF(C1>0,C1,IF(D1>0,D1))))

    although there are better answers

  6. #6
    Forum Contributor
    Join Date
    08-04-2014
    Location
    Riga
    MS-Off Ver
    2010
    Posts
    579

    Re: conditional sum (search for first cell containing a value)

    Quote Originally Posted by Bob Phillips View Post
    You could simplify that to

    =E1-IF(A1>0,A1,IF(B1>0,B1,IF(C1>0,C1,IF(D1>0,D1))))

    although there are better answers
    Hey, whatever works, right?

  7. #7
    Registered User
    Join Date
    11-06-2014
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: conditional sum (search for first cell containing a value)

    Hey these all look great, thanks!
    None of them are returning any values for me though.
    Any way of changing it so that it says something like If(A1 is no blank, E1-A1
    ...or something. Sorry I'm pretty new to excel

  8. #8
    Registered User
    Join Date
    11-06-2014
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: conditional sum (search for first cell containing a value)

    WAIT! Looks like it does work! I think I'm just copying it in wrong...gimmee a minute and I'll give credit where credit's due. Thanks everyone :D

  9. #9
    Registered User
    Join Date
    11-06-2014
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: conditional sum (search for first cell containing a value)

    Hi Bob, this works perfectly when I make a test model using actually those cell references. For some reason when I try it on my ACTUAL spreadsheet (with the cell references changed) it comes back as #Value!
    Should it matter that my A1,B2,C3 etc are not actually next to each other?? I'm really confused as to why it isn't working

  10. #10
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: conditional sum (search for first cell containing a value)

    Actually, Fotis' formula is not that different but better than mine, as I have an unnecessary coercion, and I test for > 0, he allows for negatives. But even there, it doesn't allow for a true 0, so the best in my view is

    =E1-INDEX(A1:D1,MATCH(TRUE,INDEX(A1:D1<>"",0),0))


    But getting back to your issue, you never mentioned B2, C3, you said A1:D1. Are you saying that the value are in A1, B2, C3 and say D4? If so, it can be done, but it is more complex

    =E1-INDEX(N(OFFSET(A1,{0,1,2,3},{0,1,2,3},1,1)),(MATCH(TRUE,INDEX(N(OFFSET(A1,{0,1,2,3},{0,1,2,3},1,1))<>0,0),0)))

  11. #11
    Registered User
    Join Date
    11-06-2014
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: conditional sum (search for first cell containing a value)

    Sorry, I made a mistake in my last message - it has been a long day looking a spreadsheets. I had meant to say A1:D1 (ignore my A1 B2 C2 comment).

    What I mean is that I said A1:D1 hypothetically but in actual fact my "A1:D1" are: I4, N4, S4, X4, AC4, AH4,AM4 and my "E1" is: AN4

    Using this model =E1-IF(A1>0,A1,IF(B1>0,B1,IF(C1>0,C1,IF(D1>0,D1))))
    Surely my formula should be:

    =AN4-IF(I4>0,I4,IF(N4>0,N4,IF(S4>0,S4,IF(X4>0,X4,IF(AC4>0,AC4,IF(AH4>0,AH4,IF(AM4>0,AM4)))))))
    ??

    But I can't get it to work. I think I have gone number blind, please guide me across the road to safety

  12. #12
    Registered User
    Join Date
    11-06-2014
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: conditional sum (search for first cell containing a value)

    I have just realised why it doesn't work...the cells I'm referencing are not truly blank but contain a formula. How do I get around this??

  13. #13
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: conditional sum (search for first cell containing a value)

    As BOB previously mentioned you are not clear for 1 more time. Because of your hard day?

    Check a small sample with formulas(you didn't mentioned what kind of formula are you using) in A1:D1.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    11-06-2014
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: conditional sum (search for first cell containing a value)

    Arghh I'm going crazy. I'm just going to give up for now. Sorry for bothering you all... you have been so helpful and patient!! Unfortunately I cannot upload an example as it is sensitive data. Thanks so much for all your help C:

  15. #15
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: conditional sum (search for first cell containing a value)

    A time out, looks a nice idea! Tomorrow is another day!

  16. #16
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: conditional sum (search for first cell containing a value)

    Quote Originally Posted by gjames21 View Post
    Arghh I'm going crazy. I'm just going to give up for now. Sorry for bothering you all... you have been so helpful and patient!! Unfortunately I cannot upload an example as it is sensitive data. Thanks so much for all your help C:
    Create an example and change the data to some made-up names/values.

  17. #17
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: conditional sum (search for first cell containing a value)

    Quote Originally Posted by gjames21 View Post
    Sorry, I made a mistake in my last message - it has been a long day looking a spreadsheets. I had meant to say A1:D1 (ignore my A1 B2 C2 comment).

    What I mean is that I said A1:D1 hypothetically but in actual fact my "A1:D1" are: I4, N4, S4, X4, AC4, AH4,AM4 and my "E1" is: AN4

    Using this model =E1-IF(A1>0,A1,IF(B1>0,B1,IF(C1>0,C1,IF(D1>0,D1))))
    Surely my formula should be:

    =AN4-IF(I4>0,I4,IF(N4>0,N4,IF(S4>0,S4,IF(X4>0,X4,IF(AC4>0,AC4,IF(AH4>0,AH4,IF(AM4>0,AM4)))))))
    ??

    But I can't get it to work. I think I have gone number blind, please guide me across the road to safety
    Your formula works for me, but modifying my suggestion you would use

    =AN4-INDEX(N(OFFSET(I4,0,{0,1,2,3,4,5,6}*5,1,1)),(MATCH(TRUE,INDEX(N(OFFSET(I4,0,{0,1,2,3,4,5,6}*5,1,1))<>0,0),0)))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Search column for string and date then search adjacent cell and pop up message
    By ftwobtwo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-06-2014, 09:55 AM
  2. [SOLVED] extend search formula to mutiple search for single a cell
    By lagiosman in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-20-2014, 01:16 PM
  3. IF(count(search) Function not working when search from text from a cell
    By joshnathan in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-09-2013, 05:13 AM
  4. Replies: 5
    Last Post: 03-29-2012, 04:12 AM
  5. Advance Search and GoTo a cell that meets a certain search criteria
    By moatik in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-21-2010, 04:01 PM

Tags for this Thread

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