# sum if and Vlook up

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

3. ## 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. ## Re: sum if and Vlook up

=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))

5. ## Re: sum if and Vlook up

Perfect - thank you it now all works!
cheers

6. ## 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))

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

#### 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