Vlookup/Sumif with numerous duplicate rows

[Brothwood]

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

 

[gaz_chops]

What results do you expect for the 3 examples above?

 

[henrik2h]

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.

 

[gaz_chops]

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))

 

[Brothwood]

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

 

[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.

Thank you for your time.

Regards
Brothwood

 

[gaz_chops]

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?

 

[gaz_chops]

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))

 

[Brothwood]

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

 

[gaz_chops]

Did you try my last formula?