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

[Zac]

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?

 

[RoryA]

Are your mammoth formulas adding up several other SUM formulas? (I'm having problems picturing your data layout)

 

[Peter_SSs]

Zac

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

Excel Workbook
	A	B	C	D
1		1		Sum of Total rows
2		2		34
3		3
4		4
5		5
6		6
7	Total A	21
8
9		2
10		5
11		1
12	Total B	8
13		2
14		3
15	Total C	5
16
SUMIF

 

[Jonmo1]

I think you're looking for SUMIF

=SUM(CriteriaRange,Criteria,SumRange)

Hope this helps..

 

[Zac]

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.

 

[Zac]

The SUMIF worked great.

THANK U THANK U THANK U