I am working on an inventory problem. We manufacture espresso machines. I have one file with all of the bills of materials for all of the machines listed vertically on one tab, sheet 2. There are common parts in the machines so many parts will show up repeatedly with different quantities.
I want to summarize the quantities for each of the parts in another tab, sheet 1. I was hoping to use a sumif to accomplish this.
This is the summary File, sheet 1:
A B C
Row Labels Sum of Qty Sum of Qty
1 98916 1 1
2 1110-030 29 3
3 1110-081 130 3
4 1110-140 14 3
5 1110-144 14 3
This is the file with detail file of all of the bills of material, sheet 2:
A B C
1 Item # Description Qty
2 98916 SCOOP-DECAFF COFFEE-1000i 1
3 1110-030 PLATE COVER RFR TRAY SNSR 3
4 1110-030 PLATE COVER RFR TRAY SNSR 12
5 1110-030 PLATE COVER RFR TRAY SNSR 14
6 1110-030 PLATE COVER RFR TRAY SNSR -
7 1110-081 METAL FAB LIMITER SWITCH 1
This is the sumif I am using: =SUMIF($A$2:$A$1064,'Sheet 2'!A2,'Sheet 2'!$C$2:$C$4692)
The above sumif is in cell C1 of the summary. Column B in the summary is what the quantity should be.
Any suggestions will be greatly appreciated. If I can get this to work it will be a real time saver.
Bookmarks