I need a Vlookup formula which will return 0 for #N/A and numbers below 50 will return 0 as well and that will sum up duplicate UPCS.
I have attached excel sheet, I need a vlookup for column F which is UPC to sum duplicate UPC for APPLE and for the pear since it is below 50 to return 0 and for orange #N/A to return 0.
For Column E it should do the same as well.
I'm unclear about your request and your workbook. You mention you want a vlookup but I'm not sure what you are looking up? And you mention sum up duplicates and a vlookup will simply return a value, it sounds like you want a sumif.
And if you are looking up something with values to the left a vlookup without contorting the formula looks up items to the right, for looking up things to the left you need an index/match.
Could you reload your data with expected results?
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Sure, please see attached.
ok, both of these are for the CATALOG tab, this for G2 =IFERROR(IF(SUMIF(Inventory!C:C,CATALOG!$F2,Inventory!D:D)<50,0,SUMIF(Inventory!C:C,CATALOG!$F2,Inventory!D:D)),0)
and this for H2 =IFERROR(IF(SUMIF(Inventory!C:C,CATALOG!$F2,Inventory!E:E)<50,0,SUMIF(Inventory!C:C,CATALOG!$F2,Inventory!E:E)),0)
Thanks worked!
Sorry one more thing, can you also add in formula if UPC is 0 to automatically put qty to 0 as well?
That formula will put a 0 in for a blank UPC or a 0 UPC in column F of the CATALOG tab.
Also when UPC is 0 in inventory tab of sheet under quantity in stock and Qty on PO
it will still return a 0 if the UPC in CATALOG is blank or has a 0 and it will return a 0 if the UPC in Inventory is blank or 0. Test it in your sample sheet.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks