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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Taul

Well-known Member
Joined
Oct 24, 2004
Messages
761
Office Version
  1. 2019
  2. 2016
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

Excel 2010
ABCDE
1SIZESCHMTLSHOP/FLDLABOR
226StdCSS9.5
Sheet1


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
634
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
24,220
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,531
Messages
5,854,284
Members
431,636
Latest member
shabbas313

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