Find identical value of cell thru all sheets. Sum the value to the left of that cell.

[Sylcarry87]

Hello. I have a master Bill Of Material sheet that I use to make BOMs, as options change for the order I delete sheets for those options not used. Each BOM will have far less data then the master, and some cells will change based on the needs of that particular job.

The BOM has multiple part numbers across sheets that are identical(part numbers). I cannot guarantee that the cells will be in the exact same place(row) for custom sheets I create. The quantity of each part number(cell to the left) changes as well, this is what I want to be summed up.

When I enter this data into our purchasing system I cannot create duplicate part numbers, thus I have to (tediously) find the previous entry and update the quantity. This is what I am trying to fix.

Here is some sample data: The first 3 columns are always stand alone columns with an item number I reference in drawings, the quantity that I want to sum up based upon, the part number that may or may not exist across multiple sheets. D and E are merged as well as f,g,h, and i. But these can be ignored!

A	B	C	D	E	F	G	H	I	J
ITEM #	QTY	PART NO.	MANUFACTURER	DESCRIPTION	ITEM
140	95	3031720	PHOENX CONTCT	28-12AWG STS 2.5-TWIN TERMINAL
141	8	3031733	PHOENX CONTCT	28-12AWG STS 2.5-TWIN GROUND TERMINAL
142	3	3031762	PHOENX CONTCT	28-12AWG STS 2.5-TWIN TERMINAL END COVER
143	0.25	3033710	PHOENX CONTCT	28-12AWG JUMPER STRIP
144	15	0800886	PHOENX CONTCT	TERMINAL END ANCHOR

<colgroup><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

If it is a matter of finding the duplicate part numbers and creating a "SUM" sheet that tallies these up I could be okay with that. My unicorn would be for excel to find duplicate part numbers without any input from me and total up the quantities tab for each duplicate found, in a "SUM" sheet, which will now exist in every BOM.


To summarize: I have part number 3031720 in sheets 1, 3, 4, and 7.
Part number will always be in column C. Quantity will always be in column B. But I don't know which row they will be in.

Excel finds part number in column C and takes quantity from column B and adds it up for all instances it finds, giving me a total for the entire workbook.


I could work with this scenario as well. I know most of the duplicates(and will add in new ones as I find them). I create a totals sheet with those part numbers, excel looks for these part numbers and takes the quantity from each instance so it can be summed up.

 

[emmarahudson]

Hiya

If I were doing this, I would create a new tab with all of the unique part numbers listed. Then, I would use the following formula:

=SUMIF('BOM1'!C:C,'Part numbers'!A1,'BOM1'!B:B)
It works across multiple sheets. You just have to add any additional sheets to the formula:

=SUMIF('BOM1'!C:C,'Part numbers'!A1,'BOM1'!B:B)+SUMIF('BOM2'!C:C,'Part numbers'!A1,'BOM2'!B:B)

Let me know if this isn't what you were looking for. The office is a bit noisy today so I was struggling to read your post properly!

 

[Sylcarry87]

Hiya

If I were doing this, I would create a new tab with all of the unique part numbers listed. Then, I would use the following formula:

=SUMIF('BOM1'!C:C,'Part numbers'!A1,'BOM1'!B:B)
It works across multiple sheets. You just have to add any additional sheets to the formula:

=SUMIF('BOM1'!C:C,'Part numbers'!A1,'BOM1'!B:B)+SUMIF('BOM2'!C:C,'Part numbers'!A1,'BOM2'!B:B)

Let me know if this isn't what you were looking for. The office is a bit noisy today so I was struggling to read your post properly!

Sorry for the reply delay, been working in the shop a lot lately.

I figured that using a standalone sheet would be the best option. And this does work perfectly(THANK YOU FOR ANSWERING) as long as all the sheets are there...

What I mean is this: I take my master BOM and then whittle it down to only the options ordered. So I end up deleting sheets with options that aren't in the order. This screws with the SUMIF function when applied across multiple sheets within one formula.

Is there a way to ignore nonexistent data so my formula doesn't return #VALUE when I delete sheets that are contained within the formula. I saw elsewhere that I can create a 3D range, but its limited to certain types of expressions, SUMIF isn't supported. That would be ideal because I could then whittle down my sheets and update the sheet range once I have done so. That is only one extra step and I can live with it.

I don't want to have to go thru and delete sheets within the formula every time I create a new BOM. Its more hassle than it is worth.

Suggestions?

 

[Sylcarry87]

Answered my own question! Needed to use the IFERROR function for each instance of SUMMIF. So if the SUMIF evaluates a nonexistent sheet and returns #VALUE I will get a 0 back instead of #VALUE. Also found I needed to lock my cell range so as I dragged/applied the formula it would only increment the SUMMED SHEET NAME cell I was referencing.

Updated formula:

=
IFERROR(SUMIF('SHEET1'!C$12:C$65,'SUMMED SHEET NAME'!C12,'SHEET1'!B$12:B$65),0)
+
IFERROR(SUMIF('SHEET2'!C$12:C$65,'SUMMED SHEET NAME'!C13,'SHEET2'!B$12:B$65),0)
+
IFERROR(SUMIF('SHEET3'!C$12:C$65,'SUMMED SHEET NAME'!C14,'SHEET3'!B$12:B$65),0)