Using SUM IF and VLOOKUP in one formula

[msdclark]

I am sure this is just me not understanding where to put the parenthesis correctly but I am having a difficult time making this work.

I use VLOOKUP because I always have the item# of the product in the left hand column
For the most part these spreadsheets are always number values
I like using IF so that I can return specific results if the product is or is not found
Using the item# with VLOOKUP, I am pulling from a column of one spreadsheet 'Latest' and subtracting the value of whatever is in the column of another spreadsheet 'Previous'

This is how I set it up, but it is not working -
=SUM(IF((VLOOKUP($A3,Latest!$A:$BY,8,FALSE)="","",VLOOKUP($A3,Latest!$A:$BY,8,FALSE)))-(IF((VLOOKUP($A3,Previous!$A:$BY,8,FALSE)="","",VLOOKUP($A3,Previous!$A:$BY,8,FALSE)))

I use these two formulas successfully so I am trying to combine the ideas:
=IF(VLOOKUP($A3,Latest!$A:$BY,8,FALSE)="","",VLOOKUP($A3,Latest!$A:$BY,8,FALSE))
=SUM((VLOOKUP($A3,Latest!$A:$BY,8,FALSE)-(VLOOKUP($A3,Previous!$A:$BY,8,FALSE))))

For the end result, I want the cell value in the resulting worksheet to show as blank unless there is an actual value that would come from the SUM formula. I have way too many zeros and it makes it difficult to notice the the real changes in value

Can anyone help me combine this to work?

[Pete_UK]

You don't say what you mean by "not working" - is it that you get a #VALUE or #NUM error? If so, that is because you are trying to use an arithmetic operation (i.e. minus) on a string value (i.e. ""). It might be better to have your IF function testing the result, and if it is zero then return blank (although zero might be a valid result).

Hope this helps.

Pete

[msdclark]

Not Working = Excel has a pop-up window that reads 'The Formula you typed contains an error'

I am not thoroughly experienced in Excel formulas and learn each time I work on a new one, typically by learning from boards like this.

I do not understand how to have the IF function test the result. If I am placing it in front of the SUM in the formula, how do I do this?

[msdclark]

Any other assistance?

[63falcondude]

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[msdclark]

I uploaded the file, but I can't see where it lands in the message board.

So, if looking on the Quick Ref tab, you can see in column Q and R the Y or N reference calculates wrong if the cell it is pulling from is blank. So R4 is showing Y, but it shouldn't because there is nothing in the cell that it is calculating from.

[63falcondude]

This is a response to post #6 only.

Try this in R2:

=IF(AND(Z2>1,Z2<>""),"Y","N")

Drag the formula down.

[msdclark]

And for the win.... thank you! I figured it was something simple but I was missing how to put it together.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.