solution for multiple varialbles required

bubba622

New Member
Joined
Aug 28, 2013
Messages
12
I am attempting to create a formula for selecting the correct labor hours for a piping project...there are 4 criteria that have to be met or matched in order for the correct labor to be selected...(size, sch, mtl shop/fld,)...an abbreviated version of the table is as below...
SIZE
SCH
MTL
SHOP/FLD
LABOR
36
STD
CS
S
18
30
STD
CS
S
12
26
STD
CS
S
9.5
36
XH
CS
S
32
30
XH
CS
S
18
26
XH
CS
S
12.5
36
10S
SS
S
140
30
10S
SS
S
125
26
10S
SS
F
100

<tbody>
</tbody>


Ideally what I would like to happen, on a different worksheet, is to create a formula in a cell, that says, for example, if size=36, sch=std, mtl=cs, shop/fld=s then labor (in that cell) =18 ...is there a function in excel that will determine that all criteria has been met and select the correct labor figure...any help would be appreciated....thanks
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
748
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hi,
Try this

Excel 2010
ABCDE
1SIZESCHMTLSHOP/FLDLABOR
236STDCSS18
330STDCSS12
426STDCSS9.5
536XHCSS32
630XHCSS18
726XHCSS12.5
83610SSSS140
93010SSSS125
102610SSSF100

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">SIZE</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">SCH</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">MTL</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">SHOP/FLD</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">LABOR</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">26</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Std</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">CS</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">S</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FCD5B4;;">9.5</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

formula in cell E2
Code:
=IFERROR(INDEX(Sheet2!E$2:E$10,MATCH(1,INDEX((Sheet2!A$2:A$10=A2)*(Sheet2!B$2:B$10=B2)*(Sheet2!C2:C10=C2)*(Sheet2!D2:D10=D2),),FALSE)),"not listed")

Paul.
 

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
614
Office Version
  1. 2016
Platform
  1. Windows
Assuming your example data is in columns A to E, and the result you want will always be a number, try sumifs (like a sumif, but allowing multiple criteria)
=sumifs(E:E,A:A,36,B:B,"std",C:C,"cs",D:D,"s")
Remember to add the data table sheet name to the ranges if you're putting the formula in a different sheet.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
How about =SUMIFS(E:E, A:A, 36, B:B, "std", C:C, "cs", D:D, "s")

Replacing the constants with their respective input cells.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,467
Members
414,452
Latest member
Dannysamworth

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