# SUM (there's go to be an easier way)

#### Zac

##### Well-known Member
I have 1000 rows for an Indented BOM (Bill of Materials) for an assembly.

There are several sub assys which lists their components undeath each and the costs associated with each. The Sub Assy has a total cost roll-up for each (L10:Y10).

There are several sub assembly totals because of the qty's being ordered by the customer on any given time.

Here's what the fx looks like:
=L12+L21+L22+L24+L25+L39+L40+L41+L205+L220+L221+L222+L424+L425+L453+L454+L742+L753+L758+L759+L760+L819+L820+L821+L822+L823+L824+L825

=M12+M21+M22+M24+M25+M39+M40+M41+M205+M220+M221+M222+M424+M425+M453+M454+M742+M753+M758+M759+M760+M819+M820+M821+M822+M823+M824+M825

etc

I was thinking of adding a column to code the sub assy's, then have a formula that would possibly use an Index-Match-Sum sort of fx but I ahevn't quite figured that out yet and I'm still working on it.

Any suggestions as to how I can grab the totals for each S/A without a behemoth of a SUM fx?

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Are your mammoth formulas adding up several other SUM formulas? (I'm having problems picturing your data layout)

Zac

Your layout is not clear to me, but would the SUMIF function be any use?

Excel Workbook
ABCD
11Sum of Total rows
2234
33
44
55
66
7Total A21
8
92
105
111
12Total B8
132
143
15Total C5
16
SUMIF

I think you're looking for SUMIF

=SUM(CriteriaRange,Criteria,SumRange)

Hope this helps..

Here's a simple layout.
Here's how the sheet is set up:

H I J K L M N
P/N Desc Qty Unit Cost Unit Cost Unit Cost
09 A Parent Assy \$16000 \$16000 \$16900
10 A xxx x \$1500 \$1500 \$1600
11 B \$600 \$600 \$600
12 C \$500 \$500 \$600
13 D \$400 \$400 \$400
14 E xxx x \$900 \$900 \$1200
15 F 15 25 100
16 G 35 100 200
17 H 26 75 100
18 I xxx x \$800 \$1200 \$1300
19 J xxx x \$1100 \$1100 \$1500
20 K

The Formula in L9 above will Look like this =L10+L14+L18+L19.
M9 would do the same. =M10+M14+M18+M19.

The total in L10 would sum all the components in the assembly. L11:L13.
L14 would sum L15:17.
L18 & L19 might not have a parts list and this is an off-the-shelf item from an OEM source.

The costs for the items in each Sub Assy I don't want to grab so the user created this huge fx as mentioned in my orig post.

Hope this makes sense.
I might have to create an HTML for you to see.

The SUMIF worked great.

THANK U THANK U THANK U

1,196,130
Messages
6,013,624
Members
441,777
Latest member

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