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  Register To Reply

2. Re: sum if and Vlook up  Register To Reply

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  Register To Reply

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))  Register To Reply

5. Re: sum if and Vlook up

Perfect - thank you it now all works!
cheers  Register To Reply

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))  Register To Reply