Multiple Criteria to Return Value

[nypedestrian]

Hi All,

I've been stumped by this issue. I have tried using INDEX and MATCH functions but this is my first time using them so I cannot track where the problem is coming from.

TIMECARD SHEET - Summary Sheet - This is where I need to enter the value from a table in another sheet.

Employee Name

Smith, John

<tbody>
</tbody>

Date Hours Rate Cash Tips CC Tips Tips Total Total Pay 9/29/14 FORMULA NEEDED 9.00 <tbody> </tbody>

PAYROLL SHEET - Data Sheet - Sheet where I pull information from

<style>table { }td { padding: 0px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl84 { color: white; font-weight: 700; border-width: 0.5pt medium; border-style: solid none; border-color: black -moz-use-text-color; background: none repeat scroll 0% 0% black; }.xl85 { text-align: center; border-width: 0.5pt medium; border-style: solid none; border-color: black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(217, 217, 217); }.xl86 { border-width: 0.5pt medium; border-style: solid none; border-color: black -moz-use-text-color; background: none repeat scroll 0% 0% rgb(217, 217, 217); }.xl87 { text-align: center; border-width: 0.5pt medium; border-style: solid none; border-color: black -moz-use-text-color; }.xl88 { border-width: 0.5pt medium; border-style: solid none; border-color: black -moz-use-text-color; }.xl89 { color: white; font-weight: 700; text-align: center; border-width: 0.5pt medium 0.5pt 0.5pt; border-style: solid none solid solid; border-color: black -moz-use-text-color black black; background: none repeat scroll 0% 0% black; }.xl90 { border-width: 0.5pt medium 0.5pt 0.5pt; border-style: solid none solid solid; border-color: black -moz-use-text-color black black; background: none repeat scroll 0% 0% rgb(217, 217, 217); }.xl91 { border-width: 0.5pt medium 0.5pt 0.5pt; border-style: solid none solid solid; border-color: black -moz-use-text-color black black; }.xl92 { color: white; font-weight: 700; text-align: center; border-width: 0.5pt medium; border-style: solid none; border-color: black -moz-use-text-color; background: none repeat scroll 0% 0% black; }</style> Date Employee Name Payroll Category Hours 9/29/14 Doe, John Hourly 1 9/29/14 Smith, John Hourly 9 9/29/14 Smith, John Cash Tips 9/29/14 Smith, John CC Tips 9/30/14 Smith, John Hourly 10 9/30/14 Smith, John CC Tips <tbody> </tbody>

<tbody>
</tbody>

I want excel to match the following criteria from TIMECARD SHEET and PAYROLL SHEET to return HOURS value: Date, Employee Name and (if needed) PAYROLL CATEGORY = Hourly.
In the case of the data above, 9 would be the returned based on the criteria. Thank you so much in advance for any help or direction you can give me.

 

[steve the fish]

Hi.

That's not possible how its set up. You will need to provide the payroll category on your summary sheet for the formula to use.

 

[steve the fish]

Unless its always 'hourly' you want?

 

[DLB20720]

You might be able to pull that off if you can Concatenate all values and use that as a MATCH and INDEX value.

 

[t0azt]

Try
=SUMIFS(payroll!$D$2:$D$7,payroll!$C$2:$C$7,"HOURLY",payroll!$B$2:$B$7,timecard!B1,payroll!$A$2:$A$7,timecard!A5)

Assuming employee name and smith, john are in A1 and A2 repectiveley on the timecard sheet and the table starts on A4. And your payroll sheet starts on A1.