+ Reply to Thread
Results 1 to 6 of 6

sum if and Vlook up

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    Oxford, England
    MS-Off Ver
    Excel 2010
    Posts
    41

    sum if and Vlook up

    Hi

    I am trying to set up a formula that will look at one cell (DG15) on a worksheet and then take it away from a different cell (AG15) - however if that cell is blank then it will look at a different cell (BT15) and use that to calculate.

    this is the formula that I am using at the moment:

    =SUM(IF('Data Entry'!AG15=0,(VLOOKUP('Data Entry'!DG15,Formulas!$B:$BW,74,FALSE)-VLOOKUP('Data Entry'!BT15,Formulas!$B:$BW,74,FALSE))),(VLOOKUP('Data Entry'!DG15,Formulas!$B:$BW,74,FALSE)-(VLOOKUP('Data Entry'!AG15,Formulas!$B:$BW,74,FALSE))))

    however what this is doing is:

    assume AG15 is blank - it this then doing as I ask in the sense that it is taking DG15( 12) - BT15(15) - which produces -3. At this point I want it to give me that answer. However it then goes on to do DG15(12) - AG15(0) = 12 and then does -3 +12 = 9.

    When AG15 = a number it does seem to just do the second part of my formula and not the first - which is what I want. It is just the other way round it doesn't seem to work!!

    thanks

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: sum if and Vlook up

    Could you post your workbook?
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: sum if and Vlook up

    Going by your first sentence, you would need this:

    =IF(AG15="",BT15,AG15)-DG15

    I'm not sure where your VLOOKUPs come into it.

    Hope this helps.

    Pete

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: sum if and Vlook up

    without recreating your work book
    =IF('DATA ENTRY'!AG15=0,VLOOKUP('DATA ENTRY'!DG15,FORMULAS!$B:$BW,74,FALSE)-VLOOKUP('DATA ENTRY'!BT15,FORMULAS!$B:$BW,74,FALSE),VLOOKUP('DATA ENTRY'!DG15,FORMULAS!$B:$BW,74,FALSE)-VLOOKUP('DATA ENTRY'!AG15,FORMULAS!$B:$BW,74,FALSE))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Registered User
    Join Date
    06-03-2013
    Location
    Oxford, England
    MS-Off Ver
    Excel 2010
    Posts
    41

    Re: sum if and Vlook up

    Perfect - thank you it now all works!
    cheers

  6. #6
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: sum if and Vlook up

    A little shorter:
    =VLOOKUP('Data Entry'!DG15,Formulas!$B:$BW,74,FALSE)-IF('Data Entry'!AG15=0,VLOOKUP('Data Entry'!BT15,Formulas!$B:$BW,74,FALSE),VLOOKUP('Data Entry'!AG15,Formulas!$B:$BW,74,FALSE))

+ 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. Vlook up help
    By TrainerJ in forum Excel General
    Replies: 4
    Last Post: 01-20-2010, 09:10 AM
  2. If with Vlook up
    By souchie40 in forum Excel General
    Replies: 3
    Last Post: 09-09-2005, 03:05 PM
  3. [SOLVED] vlook up
    By KL in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 03:05 PM
  4. vlook up
    By Sean in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  5. vlook up
    By Sean in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM

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