Multiple Column - Multiple Value Costing

Jennifer Meyer

New Member
Joined
Apr 24, 2020
Messages
14
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
 

Attachments

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

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,121
Office Version
  1. 365
Platform
  1. Windows
Sorry, I missed your previous post. I'll respond as soon as I can.
 

Some videos you may like

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.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,121
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 24, 2020
Messages
14
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top