# Vlookup/Sumif with numerous duplicate rows

#### Brothwood

##### New Member
Hi all,

I was hoping for some help with a problem i have.

I am doing a report to find the amount of parts used during our production process. I have one tab with all the products, parts, amount of parts per product and total amount of products built.

Some of the parts are used in different products so i am trying to find a way to do a lookup of part number across the products, multiple it by the amount used per product and then multiple it by how many products we have built.

Below is a rough example of how it looks, there are other columns and on my database but if i can find the correct type of formula i can adjust it to my needs

 Product part no part qty built qty product1 part1 6 4 product2 part3 2 0 product3 part2 4 5 product4 part3 4 3 product5 part1 2 2 product6 part1 3 1 product7 part2 4 3 product8 part3 3 7 product9 part1 1 0

<tbody>
</tbody>

I then have another tab on my report with a list of part number
 part no total qty used part1 part2 part3

<tbody>
</tbody>

I have been playing with a few formulas but i can get one to work.

The best one i have found is using a CSE array formula {=SUM(table1!\$D\$2:\$D\$10*IF(A2=TRANSPOSE(table1!\$B\$2:\$B\$10),TRANSPOSE(table1!\$C\$2:\$C\$10),0))}

This is giving me crazy values and i have been searching around for other ways to get the results i want but i cant figure it out.

Any help would be greatly appreciated.

Regards
Brothwood

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
What results do you expect for the 3 examples above?

Not exactly sure what you are after but it sound using pivot tables would do it?

Or use sumifs (or sumproduct) which takes criterias such as sum all "part qty" where it says "part 1" in column B. You could include multiple criterias such as Product = product1 AND part no = part1.

Try

=SUMIF(table1!\$B\$2:\$B\$10,\$A2,table1!\$C\$2:\$C\$10)*SUMIF(table1!\$B\$2:\$B\$10,\$A2,table1!\$D\$2:\$D\$10)

Or probably

=SUMPRODUCT((table1!\$B\$2:\$B\$10=\$A2)*(table1!\$C\$2:\$C\$10)*(table1!\$D\$2:\$D\$10))

Last edited:
What results do you expect for the 3 examples above?

Part1 should = C2*D2+C6*D6+C7*D7+C10*D10
Part2 = C4*D4+C8*D8
Part3 = C3*D3+C5*D5+C9*D9

Try

=SUMIF(table1!\$B\$2:\$B\$10,\$A2,table1!\$C\$2:\$C\$10)*SUMIF(table1!\$B\$2:\$B\$10,\$A2,table1!\$D\$2:\$D\$10)

Or probably

=SUMPRODUCT((table1!\$B\$2:\$B\$10=\$A2)*(table1!\$C\$2:\$C\$10)*(table1!\$D\$2:\$D\$10))

The SUMPRODUCT formula seems to be working, just need to change the critera to reflect my report to make sure it works across the board.

My report has 103 different products with a mixture of 44 parts, some with *** varients which need to be tested to make sure they all work correctly,

but thank you for your help, sometimes i try to over complicate things.

Regards
Brothwood

Try

=SUMIF(table1!\$B\$2:\$B\$10,\$A2,table1!\$C\$2:\$C\$10)*SUMIF(table1!\$B\$2:\$B\$10,\$A2,table1!\$D\$2:\$D\$10)

Or probably

=SUMPRODUCT((table1!\$B\$2:\$B\$10=\$A2)*(table1!\$C\$2:\$C\$10)*(table1!\$D\$2:\$D\$10))

Hi Gaz,

So i have tried both your formula's, the top one gives me crazy high values again like my original formula.

And the 2nd one works for parts with static part numbers, but for the parts where we have variances in the part number month by month (we use XXX**1234*** or *XXXXX* for example on our main parts list) these are giving me 0's when i know if i work it out manually there should be a value.

I tried changing the =A2 to = "*"&A2&"*" but that didnt seem to work either.

Regards
Brothwood

Hi Gaz,So i have tried both your formula's, the top one gives me crazy high values again like my original formula.And the 2nd one works for parts with static part numbers, but for the parts where we have variances in the part number month by month (we use XXX**1234*** or *XXXXX* for example on our main parts list) these are giving me 0's when i know if i work it out manually there should be a value.I tried changing the =A2 to = "*"&A2&"*" but that didnt seem to work either.Thank you for your time.RegardsBrothwood
Can you post a few real examples of your part numbers?

If it is your part numbers in table 1 that have the extra characters, try

=SUMPRODUCT((ISNUMBER(SEARCH(\$A2,table1!\$B\$2:\$B\$10)))*(table1!\$C\$2:\$C\$10)*(table1!\$D\$2:\$D\$10))

Can you post a few real examples of your part numbers?

Hi Gaz,

The main table is where i have the formatted part numbers (A2 reference) where as table1 has the actual part numbers which contain multiple variances of the same part.

table1 example

part no
 S7067WGM2NR-1T

<tbody>
</tbody>
 S7067WGM2NR-1T

<tbody>
</tbody>
 S7067WGM2NR-1T

<tbody>
</tbody>
 S7067GM2NR-1T-B-SYN

<tbody>
</tbody>
 S7067GM2NR-1T-B-SYN

<tbody>
</tbody>
 S7063WGM2NR1T-I2

<tbody>
</tbody>
 S7063WGM2NR1T-I2

<tbody>
</tbody>
 S7063WGM3NR-2T-B

<tbody>
</tbody>
 S7063WGM3NR-2T-B

<tbody>
</tbody>

<tbody>
</tbody>

Main table example

looking for part no (A2 & A3)
 *S7063*

<tbody>
</tbody>
 *S7067*

<tbody>
</tbody>

<tbody>
</tbody>

As you can see there are a few part numbers which we group under one part.

M393B2G70QH0-YK008
M393B2G70DB0-YK0

are two other part numbers we count as one part using

M393B2G70**0-YK0*

Hope this helps you understand a bit more.

Regards
Brothwood

Did you try my last formula?

Replies
3
Views
561
Replies
1
Views
797
Replies
11
Views
580
Replies
0
Views
424
Replies
6
Views
345

1,196,097
Messages
6,013,433
Members
441,766
Latest member
ixruiz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back