Vlookup and find the total for a part number

**lostinformulas** · 11-07-2006, 03:30 PM

Hi

I have a spreadsheet with thousands of lines and need to easily be able to do a Vlookup and return the total of each part number.

I have tried using the data subtotal option, but I receive the first amount instead of the total.

Part number in row A
count in row B

I need a to return a total for each part number and do a Vlookup to put that amount into another worksheet.

Thanks

Lostinformulas

**VBA Noob** · 11-07-2006, 03:35 PM

Hi,

Try

=VLOOKUP(A1,'Main Sheet'!$A:$B,2,FALSE)

Where A1 in summary sheet say is the part number you want to find

Change 'Main Sheet' to your sheet name

VBA Noob

**lostinformulas** · 11-07-2006, 03:39 PM

VBA Noob

Column B also houses the indivual counts, how will the look return the total?

Lostinformulas

**VBA Noob** · 11-07-2006, 03:51 PM

Argh,

It's a summary page your after,

Try this

Assumes the code you want to sum total of is starts in A1.
Again change sheets names as required

=SUMPRODUCT(--('Main Sheet'!A2:A10000=Summary!A1)*('Main Sheet'!B2:B10000))

VBA Noob

**lostinformulas** · 11-07-2006, 04:38 PM

VBA Noob

Yes I want a summary sheet.

I tried the formula, I'm not sure what I did wrong.

Where do I need to put the sum product formula, in a different sheet.

Currently the sheet that houses my part numbers is label Sheet 1.

Sheet 1 has many part numbers, how will this formula display the totals for all of the part numbers?

Lostinformula

Now you can see how I came up with my screen name.

**duane** · 11-07-2006, 04:46 PM

assuming that the list of part numbers and counts are in columns a and b on sheet1, and the part number to be summed is in cell a1 of main sheet

=SUMPRODUCT((sheet1!A2:A10000='main sheet'!A1)*(sheet1!B2:B10000))

**VBA Noob** · 11-07-2006, 04:48 PM

Hi,

You can use Data>Filter>Advanced Filter to get your list for the summary sheet.

"Copy to a new location" and "Unique records only"

Filtering Tutorial at Debra Dalgleish's site.......
http://www.contextures.on.ca/tiptech.html

Then enter this in B1 on your summary sheet

=SUMPRODUCT(--(Sheet1!A2:A10000=Summary!A1)*(Sheet1!B2:B10000))

If the summary sheet is still sheet2 then use this

=SUMPRODUCT(--(Sheet1!A2:A10000=Sheet2!A1)*(Sheet1!B2:B10000))

VBA Noob

Vlookup and find the total for a part number

LinkBack

Thread Tools

Rate This Thread

Display

Vlookup and find the total for a part number

Vlookup

Vlookup and find the total for a part number

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions