Multiple Criteria to Return Value

nypedestrian

New Member
Joined
Oct 7, 2014
Messages
1
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 NameSmith, John

<tbody>
</tbody>


Date
Hours
Rate
Cash Tips
CC Tips
Tips Total
Total Pay
9/29/14FORMULA NEEDED9.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>
DateEmployee NamePayroll CategoryHours
9/29/14Doe, JohnHourly1
9/29/14Smith, JohnHourly9
9/29/14Smith, JohnCash Tips
9/29/14Smith, JohnCC Tips
9/30/14Smith, JohnHourly10
9/30/14Smith, JohnCC 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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
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.
 

DLB20720

Board Regular
Joined
Sep 29, 2013
Messages
117
You might be able to pull that off if you can Concatenate all values and use that as a MATCH and INDEX value.
 

t0azt

Board Regular
Joined
Aug 1, 2012
Messages
134
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,631
Messages
5,523,992
Members
409,554
Latest member
denistrevisan

This Week's Hot Topics

Top