Vlookup/Sumif with numerous duplicate rows

Brothwood

New Member
Joined
Mar 16, 2015
Messages
33
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

Productpart nopart qtybuilt qty
product1part164
product2part320
product3part245
product4part343
product5part122
product6part131
product7part243
product8part337
product9part110

<tbody>
</tbody>

I then have another tab on my report with a list of part number
part nototal 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 calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Reading again I think this might work?

If not post an example

Change ASH13 & S7067 to be the cell ref's
=SUMPRODUCT((ISNUMBER(SEARCH(ASH13,$B$3:$B$12)))*(ISNUMBER(SEARCH(S7067,$A$3:$A$12)))*($C$3:$C$12)*($D$3:$D$12))
 
Upvote 0
Reading again I think this might work?

If not post an example

Change ASH13 & S7067 to be the cell ref's
=SUMPRODUCT((ISNUMBER(SEARCH(ASH13,$B$3:$B$12)))*(ISNUMBER(SEARCH(S7067,$A$3:$A$12)))*($C$3:$C$12)*($D$3:$D$12))

Hi Gaz,

I tried this and i didnt get the result i was looking for, just came up with 0 when i knew there should be more

Part Number
Part type 1-1
Mfr
Qty
Part type 1-2
Mfr
Qty
Part type 2-1
Mfr
Qty
Part type 2-2
Mfr
Qty
Part type 3-1
Mfr
Qty
Part type 4-1
Mfr
Qty
Part type 4-2
Mfr
Qty
Part type 5-1
Mfr
Qty
Qty
ASH13
MZ7WD960HAGP-00003
14
S7067WGM2NR-1T
1
HMT42GR7AFR4A-PB
8
DPS-500AB-10C
1
10
ASH13-3
MZ7WD960HAGP-00003
14
S7067WGM2NR-1T
1
HMT42GR7AFR4A-PBT
8
DPS-500AB-10C
1
90
ASH13A-2
SSDSC2BB800G4
14
S7067WGM2NR-1T
1
HMT42GR7AFR4A-PBT
8
DPS-500AB-10C
1
48
ASH13F-3
MZ7GE960HMHP-000AZ
14
S7067WGM2NR-1T
1
HMT42GR7BFR4A-PB
8
DPS-500AB-10C
1
3
BNF13-2
0F20620-AMZ
6
S7067GM2NR-1T-B-SYN
1
HMT42GR7AFR4A-PBT
8
DPS-500AB-10A-2
1
55
CLM13B-1
ST91000640NS
2
SSDSC2BB800G4
8
S7063WGM2NR1T-I2
1
M393B2G70QH0-YK008
16
DPS-650XB-1C
1
2
EMU14A
SSDSC2BB120G4
2
S7094GMR-LE-SYN-R0.3A ECN D
1
M393B2G70DB0-YK0
8
DPS-650XB-2-B-S1F
1
4
HYP13A-1
ST91000640NS
1
SSDSC2BB480G4
10
S7063WGM3NR-2T-B
1
M393B2G70DB0-YK0
8
DPS-500AB-10A-2
1
8
KNH13B
SSDSC2BB480G4
2
S7063GM2NR-1T
1
MT18KSF1G72PDZ-1G6E1
8
DPS-650XB-1C
1
7
SPC12
SSDSC2BB480G4
10
SSDSC2BB240G401
1
S7067WGM2NR-1T-B-SYNW
1
MT36KSF2G72PZ-1G6E1
8
DPS-500AB-10A-2
1
4
VGR14A
SSDSC2BB120G4
1
S7094GMR-LE-SYN-R0.3A ECN D
1
MT18JSF1G72PDZ-1G9E2
8
DPS-650XB-2-B-S1F
1
65

<tbody>
</tbody>



that is an example of table1 called "SKU", some info has been removed, but thats the basic lay out. Below is the table im working on.

I need to find the "total components tested", at the moment im using the formula you gave me,
"=SUMPRODUCT((ISNUMBER(SEARCH($E2,SKUs!$H$2:$H$20)))*(SKUs!$J$2:$J$20)*(SKUs!$Z$2:$Z$20))+SUMPRODUCT((ISNUMBER(SEARCH($E2,SKUs!$K$2:$K$20)))*(SKUs!$M$2:$M$20)*(SKUs!$Z$2:$Z$20))"

using the range 2-20 because that covers all the ASH13 varients in my actual table. then adjusting these values as i go down the varients.
My problem is if we have a new varient, it will be added to the sku table which will shift the ranges. and i will not be managing this as it will be a weekly report done by another member of staff, which is why i want it as automated as posible so mistakes can not be made.


PRODUCT
Component Type
Supplier
Part Number
Yield
Fail
Rate

Total Components
Tested

Total Components
Passed

Total Component
Failures

ASH13
type 2
MZ7WD960HAGP-00003
????
ASH13
TYPE 2
MZ7GE960HMHP-000AZ
????
ASH13
TYPE 2
SSDSC2BB800G4
????
ASH13
TYPE 2
SDLFNCAR-960G-1HA1
????
ASH13
TYPE 3
S7067WGM2NR-1T
????
ASH13
TYPE 4
HMT42GR7AFR4A-PB
????
ASH13
TYPE 4
HMT42GR7AFR4A-PBT
????
ASH13
TYPE 4
HMT42GR7BFR4A-PB
????
ASH13
TYPE 4
MT36KSF2G72PZ-1G6E1
????
ASH13
TYPE 4
M393B2G70QH0-YK008
????
ASH13
TYPE 4
M393B2G70DB0-YK0
????
ASH13
TYPE 5
DPS-500AB-10C
????
BNF13
TYPE 1
0F20620-AMZ
????
BNF13
TYPE 3
S7067GM2NR-1T-B-SYN
????
BNF13
TYPE 4
HMT42GR7AFR4A-PBT
????
BNF13
TYPE 4
HMT42GR7BFR4A-PB
????
BNF13
TYPE 4
M393B2G70DB0-YK0
????
BNF13
TYPE 4
M393B2G70QH0-YK008
????
BNF13
TYPE 5
DPS-500AB-10A-2
????
CLM13
TYPE 1
ST91000640NS
????
CLM13
TYPE 1
WD1000CHTZ
????
CLM13
TYPE 2
SSDSC2BB800G4
????
CLM13
TYPE 3
S7063WGM2NR1T-I2
????
CLM13
TYPE 4
HMT42GR7AFR4A-PBT
????
CLM13
TYPE 4
HMT42GR7BFR4A-PB
????
CLM13
TYPE 4
M393B2G70QH0-YK008
????
CLM13
TYPE 4
M393B2G70DB0-YK0
????
CLM13
TYPE 5
DPS-650XB-1C
????
EMU14
TYPE 2
SSDSC2BB120G4
????
EMU14
TYPE 3
S7094GMR-LE-SYN-R0.3A ECN D
????
EMU14
TYPE 4
M393B2G70DB0-YK0
????
EMU14
TYPE 5
DPS-650XB-2-B-S1F
????
HYP13
TYPE 1
ST91000640NS
????
HYP13
TYPE 2
SSDSC2BB480G4
????
HYP13
TYPE 3
S7063WGM3NR-2T-B
????
HYP13
TYPE 4
M393B2G70DB0-YK0
????
HYP13
TYPE 4
M393B2G70QH0-YK008
????
HYP13
TYPE 5
DPS-500AB-10A-2
????
KNH13
TYPE 2
SSDSC2BB480G4
????
KNH13
TYPE 3
S7063GM2NR-1T
????
KNH13
TYPE 4
HMT41GR7AFR8A-PB
????
KNH13
TYPE 4
M393B1G73QH0-YK008
????
KNH13
TYPE 4
M393B1G73DB0-YK0
????
KNH13
TYPE 4
MT18KSF1G72PDZ-1G6E1
????
KNH13
TYPE 5
DPS-650XB-1C
????
SPC12
TYPE 2
SSDSC2BB480G4
????
SPC12
TYPE 2
SSDSC2BB240G401
????
SPC12
TYPE 2
SSDSC2BB240G4
????
SPC12
TYPE 3
S7067WGM2NR-1T-B-SYNW
????
SPC12
TYPE 4
MT36KSF2G72PZ-1G6E1
????
SPC12
TYPE 4
HMT42GR7AFR4A-PBT
????
SPC12
TYPE 4
M393B2G70QH0-YK008
????
SPC12
TYPE 5
DPS-500AB-10A-2
????
VGR14
TYPE 2
SSDSC2BB120G4
????
VGR14
TYPE 3
S7094GMR-LE-SYN-R0.3A ECN D
????
VGR14
TYPE 4
MT18JSF1G72PDZ-1G9E2
????
VGR14
TYPE 5
DPS-650XB-2-B-S1F
????

<tbody>
</tbody>
 
Upvote 0
Ok not sure I got the ranges right when I copied and pasted the data above!

Try
B2 is the cell that has ASH13 etc, A2:A20 is ASH13-3 etc

=SUMPRODUCT((ISNUMBER(SEARCH($B2,SKUs!$A$2:$A$20)))*(ISNUMBER(SEARCH($E2,SKUs!$H$2:$H$20)))*(SKUs!$J$2:$J$20)*(ISNUMBER(SEARCH($B2,SKUs!$A$2:$A$20)))*(SKUs!$Z$2:$Z$20))+SUMPRODUCT((ISNUMBER(SEARCH($E2,SKUs!$K$2:$K$20)))*(SKUs!$M$2:$M$20)*(SKUs!$Z$2:$Z$20))
 
Upvote 0
Ok not sure I got the ranges right when I copied and pasted the data above!

Try
B2 is the cell that has ASH13 etc, A2:A20 is ASH13-3 etc

=SUMPRODUCT((ISNUMBER(SEARCH($B2,SKUs!$A$2:$A$20)))*(ISNUMBER(SEARCH($E2,SKUs!$H$2:$H$20)))*(SKUs!$J$2:$J$20)*(ISNUMBER(SEARCH($B2,SKUs!$A$2:$A$20)))*(SKUs!$Z$2:$Z$20))+SUMPRODUCT((ISNUMBER(SEARCH($E2,SKUs!$K$2:$K$20)))*(SKUs!$M$2:$M$20)*(SKUs!$Z$2:$Z$20))

Hi Gaz,

I have tried this and it is still giving me blanks where i know there should be values. I have tested the way i was doing it by just selecting the range per unit and this week we actually had a new product released. they just insert a copied row into the sku tab so it only has to be added to that products range and all is fine, sometimes if it is a low varient, nothing has to be editted, so i think i will leave it as is and just monitor it.

Thank you for all your help on this matter.

BR
Brothwood
 
Upvote 0
You're welcome, if you can upload a sample sheet to dropbox or similar (remove any sensitive data), I can take a look.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top