Multiple Column - Multiple Value Costing

[Jennifer Meyer]

Good morning,
I have a worksheet generated from another program that does not list our opening numbers in a way that I can find total cost for one opening at at time.

The worksheet has 57 columns of "Openings", inside these columns are 1483 opening #'s and each opening # may be found many times in different rows. The rows for each opening #'s is the cost for that part of the opening. I need to find a total cost for each opening.

I tried many different formulas and arrangements to be able to enter/find/sort 1 opening # from the worksheet and have it sum all associated (row costs) and could not get anything to work. I have also attached an image (Opening Costs Complete - by Opening) that I made by doing hours of cutting/pasting, and inserting rows to get all 1483 openings (from each of the 57 columns) to Column J. I inserted rows and then copied down the costs. As you can see by Row 1488/Column J, I have a formula that calculates the Net Cost after filtering by Opening #.

I know that excel is very powerful and believe there is a better way. I have to pull this report from our program frequently for different projects so I am looking to find a faster way to convert my file "Cost by Opening numbers" to what I created manually "Openings Costs Complete".

Please help!
Jennifer

 

[StephenCrump]

Sorry, I missed your previous post. I'll respond as soon as I can.

 

[Jennifer Meyer]

Sorry, I missed your previous post. I'll respond as soon as I can.

Have you had any luck with this one?

Jennifer

 

[StephenCrump]

So sorry!

I think this is what you're looking for?

	A	B	C	D	E	F	G	H	I	J	K	L
1	Product	Cost	1	2	3	4	5	6	7	8	9	10
2	A	3	999	999	999	111
3	B	1	999
4	C	1	999
5	D	5	999	999	999	999	444	555	999	111(2)
6	E	4	999			333
7	F	1	999
8	G	1	999	999
9	H	7	999	999	111 (3)	111 (7)
10	I	2	999	999			333(3)
11	J	1	999
12	K	1	999
13
14
15	Total for	Opening
16		111	83
17		333	10
18		Phase
19		A	93
20
21
22						Opening	Phase
23						111	A
24						222	B
25						333	A
26						444	B
27						555	C
Sheet1

Cell Formulas
Range	Formula
C16:C17	C16	=SUM(MMULT((TEXT(IFERROR(TRIM(LEFT(Openings,FIND("(",Openings)-1)),Openings),"0")=TEXT(B16,"0"))*IFERROR(-RIGHT(Openings,LEN(Openings)-FIND("(",Openings)+1),1),TRANSPOSE(COLUMN(Openings)^0))*Cost)
C19	C19	=SUM(MMULT(IFERROR((B19=INDEX(Phases,MATCH(TEXT(IFERROR(TRIM(LEFT(Openings,FIND("(",Openings)-1)),Openings),"0"),TEXT(INDEX(Phases,,1),"0"),),2))*IFERROR(-RIGHT(Openings,LEN(Openings)-FIND("(",Openings)+1),1),0),TRANSPOSE(COLUMN(Openings)^0))*Cost)

Named Ranges
Name	Refers To	Cells
Cost	=Sheet1!$B$2:$B$12	C19, C16:C17
Openings	=Sheet1!$C$2:$L$12	C19, C16:C17
Phases	=Sheet1!$F$23:$G$27	C19

 

[Jennifer Meyer]

Thank you Stephen,
I cannot get the C19 formula to work. I get a return of "0"

I have tried it in my workbook and also recreated a new workbook to mirror your data and same result of "0" in C19

Jennifer

 

[StephenCrump]

I cannot get the C19 formula to work. I get a return of "0"

Did you array-enter the formula?

 

[Jennifer Meyer]

I did on both the one I recreated with your info and my workbook.
CTRL + SHIFT + ENTER

 

[StephenCrump]

Did you create the range name: Phases ?

Because I have wrapped in IFERROR() you won't get the tell-tale #NAME error, the result will just be zero.

Here's My Workbook in case that helps.

 

[Jennifer Meyer]

Stephen,
Thank you for providing me your workbook. I experimented with your worksheet and am not finding the C19 formula working.

I added Opening # 111 to cell D3 and when I did the "costing" (Cell C16) updates to the new sum of 84 and Cell C19 to "0"
Shouldn't Cell C19 changed to 94 because I added one more of the 111 to data?

Jennifer