# Multiple Column - Multiple Value Costing

#### Jennifer Meyer

##### New Member
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".

Jennifer

#### Attachments

• Cost by Opening numbers.PNG
93 KB · Views: 14
• Opening Costs Complete - by Opening.PNG
58.9 KB · Views: 14

#### StephenCrump

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

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Jennifer Meyer

##### New Member
Sorry, I missed your previous post. I'll respond as soon as I can.
Have you had any luck with this one?

Jennifer

#### StephenCrump

##### MrExcel MVP
So sorry!

I think this is what you're looking for?

ABCDEFGHIJKL
1ProductCost12345678910
2A3999999999111
3B1999
4C1999
5D5999999999999444555999111(2)
6E4999333
7F1999
8G1999999
9H7999999111 (3)111 (7)
10I2999999333(3)
11J1999
12K1999
13
14
15Total forOpening
1611183
1733310
18Phase
19A93
20
21
22OpeningPhase
23111A
24222B
25333A
26444B
27555C
Sheet1
Cell Formulas
RangeFormula
C16:C17C16=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)
C19C19=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
NameRefers ToCells
Cost=Sheet1!\$B\$2:\$B\$12C19, C16:C17
Openings=Sheet1!\$C\$2:\$L\$12C19, C16:C17
Phases=Sheet1!\$F\$23:\$G\$27C19

#### Jennifer Meyer

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

##### MrExcel MVP
I cannot get the C19 formula to work. I get a return of "0"
Did you array-enter the formula?

Replies
4
Views
76
Replies
1
Views
41
Replies
1
Views
70
Replies
0
Views
38
Replies
5
Views
63

1,127,865
Messages
5,627,348
Members
416,243
Latest member
ReetuC

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