Index Match Madness

Watchdawg

Board Regular
Joined
Jan 21, 2015
Messages
63
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
ABCDE
1NameFunctionPay CodePay CatHours
2Employee 1102 Domestic - PickingBRKREGULAR - HOURLY1.100
3Employee 1104 Domestic - TandataBRKREGULAR - HOURLY1.850
4Employee 1100 Receiving Non-Forklift x Clerical UnitPaid Time Off (PTO)TAFW - PTO3.000
5Employee 1104 Domestic - TandataWRKREGULAR - HOURLY10.900
6Employee 1104 Domestic - TandataWRKREGULAR - HOURLY17.283
7Employee 1515 QA Labor SupportWRKREGULAR - HOURLY2.400
8Employee 1540 SanitationWRKREGULAR - HOURLY0.383
9Employee 2804 Inventory ControlShift Diff PremPREMIUM - SHIFT DIFFERENTIAL OT0.233
10Employee 2804 Inventory ControlShift Diff PremPREMIUM - SHIFT DIFFERENTIAL REG16.067
11Employee 2804 Inventory ControlShift Diff PremPREMIUM - SHIFT DIFFERENTIAL REG23.934
12Employee 2804 Inventory ControlWRKOVERTIME - WEEKLY 1.50.233
13Employee 3804 Inventory ControlWRKREGULAR - HOURLY16.067
14Employee 3804 Inventory ControlWRKREGULAR - HOURLY23.934
15Employee 4620 Japan inspectionsBRKREGULAR - HOURLY0.467
16Employee 4620 Japan inspectionsBRKREGULAR - HOURLY0.500
17Employee 5620 Japan inspectionsBRKREGULAR - HOURLY1.083
18Employee 5620 Japan inspectionsWRKREGULAR - HOURLY0.200
19Employee 5630 Rework 1WRKREGULAR - HOURLY5.816
20Employee 5632 Rework 3WRKREGULAR - HOURLY16.849
All Hours
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
C:CCell Valuecontains "Jury Duty"textNO
C:CCell Valuecontains "Bereavement"textNO
C:CCell Valuecontains "Holiday Pay"textNO
C:CCell Valuecontains "Paid Time Off (PTO)"textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO
B:BCell Valuecontains ""textNO


Cell Formulas
RangeFormula
B2B2=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:C20C2=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:D20D2=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:E20E2=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:B20B3=IF(A3="","",ROUND(SUMIFS(#REF!,#REF!,'Reg WHSE Billing'!A3,#REF!,'All Hours'!G$29),2))
G2:G20G2=IF(A2="","",A2)
H2:H20H2=IF(A2="","",ROUND(B2*(VLOOKUP(A2,'Pay Rates'!A:F,4,FALSE))+C2*(VLOOKUP(A2,'Pay Rates'!A:F,4,FALSE)),2))
I2:I20I2=IF(A2="","",ROUND(D2*(VLOOKUP(A2,'Pay Rates'!A:F,6,FALSE))+E2*(VLOOKUP(A2,'Pay Rates'!A:F,6,FALSE)),2))
J2:J20J2=IF(A2="","",SUM(H2:I2))
H22:J22,B22:E22B22=B1
B23:E23B23=SUM(B2:B20)
H23:J23H23=SUM(H2:H22)
C25,E25C25=SUM(B23:C23)
Named Ranges
NameRefers ToCells
'Pay Rates'!_FilterDatabase='Pay Rates'!$A$1:$F$44H2:I20
Function='All Hours'!$B:$BB2
Indirect='All Hours'!$H$2:$H$10B2
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
I think you'll want something similar to this, but it needs some further work:
Book20200428.xlsx
ABC
1NameReg Dir HoursReg Ind Hours
2Employee 10.383#REF!
3Employee 10.383#REF!
4Employee 10.383#REF!
5Employee 10.383#REF!
6Employee 10.383#REF!
7Employee 10.383#REF!
8Employee 10.383#REF!
9Employee 20#REF!
10Employee 20#REF!
Reg WHSE Billing
Cell Formulas
RangeFormula
B2:B10B2=SUMPRODUCT(--('All Hours'!A:A=A2),--('All Hours'!B:B=""),'All Hours'!E:E)
C2:C10C2=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,070
Messages
5,545,813
Members
410,708
Latest member
SanTrapGamer
Top