Hello, I have a spreadsheet that has to do with stone values for jewelry. I am not very good at excel, and the spreadsheet currently uses a lot of vlookups. Problem is, there are multiple instances where there are more than one stone for an item. I'm trying to get a total cost of the stones for each item, and obviously vlookup is only returning the first hit for each item number because that's its function.
I know I have to use another function, but I can't figure out which one.
This spreadsheet has 4 worksheets that work off each other, but my main concern is the first worksheet. It is an ODBC dump of our database which then does a lookup in the other tabs to total cost.
This is the first lookup in tab 1, it is looking up the item in tab 2(FGs with Stones)
=IF(ISNA(VLOOKUP($A2,'FGs with Stones'!$A:$AF,31,FALSE)),0,(VLOOKUP($A2,'FGs with Stones'!$A:$AF,31,FALSE)))
If it finds the item in tab 2, it looks in the corresponding column to find the total cost for the stones in the finished piece. But when there is the same item number but with different stones, it only returns the first value. I need the function to total the cost when there is multiples and return it to the above cell.
Example
A2 has item number HB128331. The lookup looks in FGs with Stones and finds HB128331. This is what it looks like in the FGs with Stones tab
HB128331 11.61
HB128331 5.61
HB128331 9.84
But the VLOOKUP only finds 11.61 and returns it to the cell. I need to add all of those values up and return them to the formula cell in A2
I have done some research and know that maybe SUMIF or one of the DB functions will work but I can't figure it out. Any help would be appreciated. Thank you
If there is any other information you need, or need me to attach a sample I will of course, but I wanted to hold off because the information would have to be changed a little for our clients privacy and I thought it was possible someone would solve this without me needing to go through the trouble.
Bookmarks