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

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
Perhaps something like this:

ABCDEFGHIJKL
1ProductCostOpen 1Open 2Open 3Open 4Open 5Open 6Open 7Open 8Open 9Open 10
2A$3.00999999999111
3B$1.00999
4C$1.00999
5D$5.00999999999999999999999111
6E$1.00999
7F$1.00999
8G$1.00999999
9H$7.00999999111
10I$1.00999999
11J$1.00999
12K$1.00999
13
14
15Total for111
16$15.00
Sheet1
Cell Formulas
RangeFormula
B16B16=SUM(IF(MMULT(--(C2:L12=B15),TRANSPOSE(COLUMN(C1:L1)^0)),B2:B12))


(You'll need to array enter this formula).
 

Jennifer Meyer

New Member
Joined
Apr 24, 2020
Messages
14
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Perhaps something like this:

ABCDEFGHIJKL
1ProductCostOpen 1Open 2Open 3Open 4Open 5Open 6Open 7Open 8Open 9Open 10
2A$3.00999999999111
3B$1.00999
4C$1.00999
5D$5.00999999999999999999999111
6E$1.00999
7F$1.00999
8G$1.00999999
9H$7.00999999111
10I$1.00999999
11J$1.00999
12K$1.00999
13
14
15Total for111
16$15.00
Sheet1
Cell Formulas
RangeFormula
B16B16=SUM(IF(MMULT(--(C2:L12=B15),TRANSPOSE(COLUMN(C1:L1)^0)),B2:B12))


(You'll need to array enter this formula).
Perhaps something like this:

ABCDEFGHIJKL
1ProductCostOpen 1Open 2Open 3Open 4Open 5Open 6Open 7Open 8Open 9Open 10
2A$3.00999999999111
3B$1.00999
4C$1.00999
5D$5.00999999999999999999999111
6E$1.00999
7F$1.00999
8G$1.00999999
9H$7.00999999111
10I$1.00999999
11J$1.00999
12K$1.00999
13
14
15Total for111
16$15.00
Sheet1
Cell Formulas
RangeFormula
B16B16=SUM(IF(MMULT(--(C2:L12=B15),TRANSPOSE(COLUMN(C1:L1)^0)),B2:B12))


(You'll need to array enter this formula).
Thank you!

I have found antoher problem and it is not related to your formula. Unfortunately the program groups some of the openings together like G202(2), this means there are two of the G202. I have many instances of these single cell values like this. Is there a formula to get these values into there own separate cells or to account for them for the value in the parenthesis? I have to account for these this value as 2 openings, not just one. Because of these cells my costs per opening is not calculating completely. Values like this G202(2), 111(3), etc are throughout many columns in my worksheet.

Jennifer
 

Attachments

  • Multiple Openings in One Cell.PNG
    Multiple Openings in One Cell.PNG
    11.5 KB · Views: 1

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
Perhaps:

ABCDEFGHIJKL
1ProductCost12345678910
2A3999999999111
3B1999
4C1999
5D5999999999999999999999111(2)
6E1999
7F1999
8G1999999
9H7999999111 (3)
10I1999999
11J1999
12K1999
13
14
15Total for111
1615
Sheet1
Cell Formulas
RangeFormula
B16B16=SUM(IF(MMULT(--(TEXT(IFERROR(TRIM(LEFT(C2:L12,FIND("(",C2:L12)-1)),C2:L12),"0")=TEXT(B15,"0")),TRANSPOSE(COLUMN(C1:L1)^0)),B2:B12))

This assumes that the only "(" character in your code will identify duplicates, and that you want to ignore everything after the "(".

If you codes can include, say 123 (large) and 123 (large)(2) then we'll need to modify the approach.

EDIT: Sorry, I just re-read your question and may be misinterpreting. In the example above, is the answer supposed to be:
3 + (5 x 2) +( 7 x 3) = 34?
 
Last edited:

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

EDIT: Sorry, I just re-read your question and may be misinterpreting. In the example above, is the answer supposed to be:
3 + (5 x 2) +( 7 x 3) = 34?

In which case ..

ABCDEFGHIJKL
1ProductCost12345678910
2A3999999999111
3B1999
4C1999
5D5999999999999999999999111(2)
6E1999
7F1999
8G1999999
9H7999999111 (3)111 (7)
10I1999999
11J1999
12K1999
13
14
15Total for111
1683
Sheet1
Cell Formulas
RangeFormula
B16B16=SUM(MMULT((TEXT(IFERROR(TRIM(LEFT(Openings,FIND("(",Openings)-1)),Openings),"0")=TEXT(B15,"0"))*IFERROR(-RIGHT(C2:L12,LEN(Openings)-FIND("(",Openings)+1),1),TRANSPOSE(COLUMN(C1:L1)^0))*Cost)
Named Ranges
NameRefers ToCells
Cost=Sheet1!$B$2:$B$12B16
Openings=Sheet1!$C$2:$L$12B16
 
Solution

Jennifer Meyer

New Member
Joined
Apr 24, 2020
Messages
14
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

I have one more request of this formula or for an add to my worksheet. Sometimes the openings are "phased" and we are asked for "phasing" costs, is it possible?

In your example, if values in C2-J12 are each in a different "phase", can I get a cost by phase if I have a list of opening and corresponding phase? Is there a way to look for through all the openings, match those with the a list of total opening to get what phase they are in and get a cost by phase?
 

Attachments

  • Opening with Phase.PNG
    Opening with Phase.PNG
    11.9 KB · Views: 1

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
I haven't provided formulas yet - just checking this is what you mean?

ABCDEFGHIJKLM
1ProductPhaseCost12345678910
2A1$3999999999111
3B1$1999
4C1$1999
5D1$5999999999999999999999111(2)
6E1$1999
7F1$1999
8G1$1999999
9H2$7999999111 (3)111 (7)
10I1$1999999
11J1$1999
12K1$1999
13
14
15Totals for:111
16Phase1$13
172$70
Sheet6
 

Jennifer Meyer

New Member
Joined
Apr 24, 2020
Messages
14
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Sorry for the delay, we got busy and this project got put to the back burner.

Yes, I think so. I want to be able to search all #'s columns D-L for a number, match that to a phase on a different worksheet, then total the costs. My #s in the D-L columns will only be in one Phase, not like you show 111 in Phase 1 and Phase 2. My phases and Items # will be on another work sheet.

Jennifer
 

Jennifer Meyer

New Member
Joined
Apr 24, 2020
Messages
14
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I haven't provided formulas yet - just checking this is what you mean?

ABCDEFGHIJKLM
1ProductPhaseCost12345678910
2A1$3999999999111
3B1$1999
4C1$1999
5D1$5999999999999999999999111(2)
6E1$1999
7F1$1999
8G1$1999999
9H2$7999999111 (3)111 (7)
10I1$1999999
11J1$1999
12K1$1999
13
14
15Totals for:111
16Phase1$13
172$70
Sheet6
Sorry for the delay, we got busy and this project got put to the back burner.

Yes, I think so. I want to be able to search all #'s columns D-L for a number, match that to a phase on a different worksheet, then total the costs. My #s in the D-L columns will only be in one Phase, not like you show 111 in Phase 1 and Phase 2. My phases and Items # will be on another work sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,843
Messages
5,627,213
Members
416,230
Latest member
jdaitchman

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