IF Sum Vlookup Query

[trish123]

I have an excel file with product codes and descriptions of products.
Each month I receive data of all the items that are sold over the course of the month.
What I want to do is create a vlookup based on the product code in my main sheet then look at the product code on the sales data sheet, to then return the value column of each time a purchase was created based on the different products. Also Without returning 'NA'

 

[awoohaw]

It would be helpful if you could share a small sample of your two data layouts. The xl2bb add in (link below) allows you to share mini workbooks including the formulas you currently have. If you can't use the add in, then please post your data as a table.

Also, knowing what version of excel you have would be very helpful for you scenario. Please update your profile with your excel version so it will appear on your button in the threads.

To answer the "without returning NA" question. Without knowing your version, the more prevalent scenarios are this:

=IFERROR(yourVLOOKUPformula,"")

IF(ISNA(yourVLOOKUPformula),"",yourVLOOKUPformula)

 

[trish123]

I have an excel file with product codes and descriptions of products.
Each month I receive data of all the items that are sold over the course of the month.
What I want to do is create a vlookup based on the product code in my main sheet then look at the product code on the sales data sheet, to then return the value column of each time a purchase was created based on the different products. Also Without returning 'NA'

=SUMPRODUCT(VLOOKUP(A:A,Sheet1!A:E,5,FALSE))
is my current formula, and I am using excel 2016.

Currently my formula only returns the first item price rather than a total if all items with a specific code. I also need it to not return NA

 

[trish123]

It would be helpful if you could share a small sample of your two data layouts. The xl2bb add in (link below) allows you to share mini workbooks including the formulas you currently have. If you can't use the add in, then please post your data as a table.

Also, knowing what version of excel you have would be very helpful for you scenario. Please update your profile with your excel version so it will appear on your button in the threads.

To answer the "without returning NA" question. Without knowing your version, the more prevalent scenarios are this:

=IFERROR(yourVLOOKUPformula,"")

IF(ISNA(yourVLOOKUPformula),"",yourVLOOKUPformula)

=SUMPRODUCT(VLOOKUP(A:A,Sheet1!A:E,5,FALSE))
is my current formula, and I am using excel 2016.

Currently my formula only returns the first item price rather than a total if all items with a specific code. I also need it to not return NA

 

[Peter_SSs]

As mentioned above ..

It would be helpful if you could share a small sample of your two data layouts.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[trish123]

As mentioned above ..

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[trish123]

I can't seem to get that working happy to send it to someone directly if that works,

 

[awoohaw]

I can't seem to get that working happy to send it to someone directly if that works,

you can post the data in a table. but that won't show the formulas you have in it, not. You would need to tell us the cell reference of the top left cell in the worksheet.
And critical formulas would need to be pasted into the post as well.

OR

You share the workbook on OneDrive or Dropbox or some other reputable file sharing source.

Best wishes.

 

[trish123]

=SUMPRODUCT(VLOOKUP($A$2:$A$8,Spend!A1:E37,5,FALSE))

This is my query, its just not adding all

you can post the data in a table. but that won't show the formulas you have in it, not. You would need to tell us the cell reference of the top left cell in the worksheet.
And critical formulas would need to be pasted into the post as well.

OR

You share the workbook on OneDrive or Dropbox or some other reputable file sharing source.

Best wishes.

 

[trish123]

SUMPRODUCT(VLOOKUP(@$A$2:$A$8,Spend!A1:E37,5,FALSE)) is my query

I want it to return the values found in the 5th column of the table array in the spend sheet in column 5 for each lookup value. i then want to sum up all these values using the SUMPRODUCT function, but at the moment it is inly returning the first product price of each product and not adding the multiple products with the same code together

I can't seem to upload, even with the downloaded XL2BB, And I don't know how to filetransfer