Index Match Madness

[Watchdawg]

This is the first tab ("All Hours"). Column B is color coded based on the category in tab 2 ("Reg WHSE Billing")
I've searched everywhere to attempt to learn the index/match formula to try and get this to work and it's been a miserable failure. The second tab needs to look at the employee name in column A, find all instances of that employee from the "All Hours" tab that match the orange color, add them up, and populate them in the "Reg WHSE Billing" tab next to that employee (That's column B, Column C will be looking at the pink color the same way). Hopefully that helps, I'm also going to attempt this XL2BB thing so you have a visual. The first one will be the "All Hours" tab, the second one the "Reg WHSE Billing" tab.

Eric Sample(2).xlsm
	A	B	C	D	E
1	Name	Function	Pay Code	Pay Cat	Hours
2	Employee 1	102 Domestic - Picking	BRK	REGULAR - HOURLY	1.100
3	Employee 1	104 Domestic - Tandata	BRK	REGULAR - HOURLY	1.850
4	Employee 1	100 Receiving Non-Forklift x Clerical Unit	Paid Time Off (PTO)	TAFW - PTO	3.000
5	Employee 1	104 Domestic - Tandata	WRK	REGULAR - HOURLY	10.900
6	Employee 1	104 Domestic - Tandata	WRK	REGULAR - HOURLY	17.283
7	Employee 1	515 QA Labor Support	WRK	REGULAR - HOURLY	2.400
8	Employee 1	540 Sanitation	WRK	REGULAR - HOURLY	0.383
9	Employee 2	804 Inventory Control	Shift Diff Prem	PREMIUM - SHIFT DIFFERENTIAL OT	0.233
10	Employee 2	804 Inventory Control	Shift Diff Prem	PREMIUM - SHIFT DIFFERENTIAL REG	16.067
11	Employee 2	804 Inventory Control	Shift Diff Prem	PREMIUM - SHIFT DIFFERENTIAL REG	23.934
12	Employee 2	804 Inventory Control	WRK	OVERTIME - WEEKLY 1.5	0.233
13	Employee 3	804 Inventory Control	WRK	REGULAR - HOURLY	16.067
14	Employee 3	804 Inventory Control	WRK	REGULAR - HOURLY	23.934
15	Employee 4	620 Japan inspections	BRK	REGULAR - HOURLY	0.467
16	Employee 4	620 Japan inspections	BRK	REGULAR - HOURLY	0.500
17	Employee 5	620 Japan inspections	BRK	REGULAR - HOURLY	1.083
18	Employee 5	620 Japan inspections	WRK	REGULAR - HOURLY	0.200
19	Employee 5	630 Rework 1	WRK	REGULAR - HOURLY	5.816
20	Employee 5	632 Rework 3	WRK	REGULAR - HOURLY	16.849
All Hours

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
C:C	Cell Value	contains "Jury Duty"	text	NO
C:C	Cell Value	contains "Bereavement"	text	NO
C:C	Cell Value	contains "Holiday Pay"	text	NO
C:C	Cell Value	contains "Paid Time Off (PTO)"	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO
B:B	Cell Value	contains ""	text	NO

Eric Sample(2).xlsm
	A	B	C	D	E	F	G	H	I	J
1	Name	Reg Dir Hours	Reg Ind Hours	OT Dir Hours	OT Ind Hours		Name	Reg Billing	OT Billing	Total Billing
2	Employee 1	0	#REF!	#REF!	#REF!		Employee 1	#N/A	#REF!	#N/A
3	Employee 1	#REF!	#REF!	#REF!	#REF!		Employee 1	#REF!	#REF!	#REF!
4	Employee 1	#REF!	#REF!	#REF!	#REF!		Employee 1	#REF!	#REF!	#REF!
5	Employee 1	#REF!	#REF!	#REF!	#REF!		Employee 1	#REF!	#REF!	#REF!
6	Employee 1	#REF!	#REF!	#REF!	#REF!		Employee 1	#REF!	#REF!	#REF!
7	Employee 1	#REF!	#REF!	#REF!	#REF!		Employee 1	#REF!	#REF!	#REF!
8	Employee 1	#REF!	#REF!	#REF!	#REF!		Employee 1	#REF!	#REF!	#REF!
9	Employee 2	#REF!	#REF!	#REF!	#REF!		Employee 2	#REF!	#REF!	#REF!
10	Employee 2	#REF!	#REF!	#REF!	#REF!		Employee 2	#REF!	#REF!	#REF!
11	Employee 2	#REF!	#REF!	#REF!	#REF!		Employee 2	#REF!	#REF!	#REF!
12	Employee 2	#REF!	#REF!	#REF!	#REF!		Employee 2	#REF!	#REF!	#REF!
13	Employee 3	#REF!	#REF!	#REF!	#REF!		Employee 3	#REF!	#REF!	#REF!
14	Employee 3	#REF!	#REF!	#REF!	#REF!		Employee 3	#REF!	#REF!	#REF!
15	Employee 4	#REF!	#REF!	#REF!	#REF!		Employee 4	#REF!	#REF!	#REF!
16	Employee 4	#REF!	#REF!	#REF!	#REF!		Employee 4	#REF!	#REF!	#REF!
17	Employee 5	#REF!	#REF!	#REF!	#REF!		Employee 5	#REF!	#REF!	#REF!
18	Employee 5	#REF!	#REF!	#REF!	#REF!		Employee 5	#REF!	#REF!	#REF!
19	Employee 5	#REF!	#REF!	#REF!	#REF!		Employee 5	#REF!	#REF!	#REF!
20	Employee 5	#REF!	#REF!	#REF!	#REF!		Employee 5	#REF!	#REF!	#REF!
21
22		Reg Dir Hours	Reg Ind Hours	OT Dir Hours	OT Ind Hours			Reg Billing	OT Billing	Total Billing
23		#REF!	#REF!	#REF!	#REF!			#N/A	#REF!	#N/A
24
25		Total Reg	#REF!	Total OT	#REF!
Reg WHSE Billing

Cell Formulas
Range	Formula
B2	B2	=SUMIFS('All Hours'!E:E,'All Hours'!A:A,'Reg WHSE Billing'!A2,'All Hours'!D:D,'All Hours'!G$29,Function,INDEX(Function,MATCH,Indirect,Function))
C2:C20	C2	=IF(A2="","",ROUND(SUMIFS(#REF!,#REF!,'Reg WHSE Billing'!A2,#REF!,'All Hours'!G$29)+SUMIFS(#REF!,#REF!,'Reg WHSE Billing'!A2,#REF!,'All Hours'!G$30)+SUMIFS(#REF!,#REF!,'Reg WHSE Billing'!A2,#REF!,'All Hours'!G$31)+SUMIFS(#REF!,#REF!,'Reg WHSE Billing'!A2,#REF!,'All Hours'!G$32)+SUMIFS(#REF!,#REF!,'Reg WHSE Billing'!A2,#REF!,'All Hours'!G$33),2))
D2:D20	D2	=IF(A2="","",ROUND(SUMIFS(#REF!,#REF!,'Reg WHSE Billing'!A2,#REF!,'All Hours'!G$34)+SUMIFS(#REF!,#REF!,'Reg WHSE Billing'!A2,#REF!,'All Hours'!G$35),2))
E2:E20	E2	=IF(A2="","",ROUND(SUMIFS(#REF!,#REF!,'Reg WHSE Billing'!A2,#REF!,'All Hours'!G$34)+SUMIFS(#REF!,#REF!,'Reg WHSE Billing'!A2,#REF!,'All Hours'!G$35),2))
B3:B20	B3	=IF(A3="","",ROUND(SUMIFS(#REF!,#REF!,'Reg WHSE Billing'!A3,#REF!,'All Hours'!G$29),2))
G2:G20	G2	=IF(A2="","",A2)
H2:H20	H2	=IF(A2="","",ROUND(B2*(VLOOKUP(A2,'Pay Rates'!A:F,4,FALSE))+C2*(VLOOKUP(A2,'Pay Rates'!A:F,4,FALSE)),2))
I2:I20	I2	=IF(A2="","",ROUND(D2*(VLOOKUP(A2,'Pay Rates'!A:F,6,FALSE))+E2*(VLOOKUP(A2,'Pay Rates'!A:F,6,FALSE)),2))
J2:J20	J2	=IF(A2="","",SUM(H2:I2))
H22:J22,B22:E22	B22	=B1
B23:E23	B23	=SUM(B2:B20)
H23:J23	H23	=SUM(H2:H22)
C25,E25	C25	=SUM(B23:C23)

Named Ranges
Name	Refers To	Cells
'Pay Rates'!_FilterDatabase	='Pay Rates'!$A$1:$F$44	H2:I20
Function	='All Hours'!$B:$B	B2
Indirect	='All Hours'!$H$2:$H$10	B2

 

[KRice]

I think you'll want something similar to this, but it needs some further work:

Book20200428.xlsx
	A	B	C
1	Name	Reg Dir Hours	Reg Ind Hours
2	Employee 1	0.383	#REF!
3	Employee 1	0.383	#REF!
4	Employee 1	0.383	#REF!
5	Employee 1	0.383	#REF!
6	Employee 1	0.383	#REF!
7	Employee 1	0.383	#REF!
8	Employee 1	0.383	#REF!
9	Employee 2	0	#REF!
10	Employee 2	0	#REF!
Reg WHSE Billing

Cell Formulas
Range	Formula
B2:B10	B2	=SUMPRODUCT(--('All Hours'!A:A=A2),--('All Hours'!B:B=""),'All Hours'!E:E)
C2:C10	C2	=IF(A2="","",ROUND(SUMIFS(#REF!,#REF!,'Reg WHSE Billing'!A2,#REF!,'All Hours'!G$29)+SUMIFS(#REF!,#REF!,'Reg WHSE Billing'!A2,#REF!,'All Hours'!G$30)+SUMIFS(#REF!,#REF!,'Reg WHSE Billing'!A2,#REF!,'All Hours'!G$31)+SUMIFS(#REF!,#REF!,'Reg WHSE Billing'!A2,#REF!,'All Hours'!G$32)+SUMIFS(#REF!,#REF!,'Reg WHSE Billing'!A2,#REF!,'All Hours'!G$33),2))

Rather than INDEX/MATCH, I'm recommending SUMPRODUCT, and the arguments fed to it are the conditions to satisfy and the hours. I'm assuming the hours to sum are found in 'All Hours'!E:E? But something needs to be done to account for the color coding. I can't tell from the example, or from the conditional formatting table, what distinguishes between, say, orange and pink. Both indicate that the entry in column B on the other sheet should be blank (""). Could you clarify, please? For demo purposes, I deleted a B column entry for Employee 1 and the formula then returned their column E time.