# solution for multiple varialbles required

#### bubba622

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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hi,
Try this

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

</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.

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.

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.

Replies
0
Views
146
Replies
4
Views
439
Replies
2
Views
148
Replies
1
Views
189
Replies
0
Views
225

1,219,792
Messages
6,150,291
Members
450,949
Latest member
faizanmalik10

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