SUMIF, INDEX, MATCH behaving oddly

wynandbecker

New Member
Joined
Feb 23, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I am collecting HOURS WORKED from a bunch of DAILY sheets with predefined tasks on them, and summarizing them on SUMMARY sheet (per task).
On the DAILY sheets there are multiple instances of these tasks (there are four separate teams performing tasks every day).

In Summery!F2 I am collecting all the hours worked per-team-per-task with INDEX and MATCH, and trying to add these together with SUMIF.
It seems to work fine on cell Summery!F10, but the rest of them are giving very confusing results (for example it throws most of the HOURS WORKED under task NONE on the summary sheet, also if I change one of the tasks on the DAILY sheet to a different selection - it comes back with completely different numbers)

Copy of DAILY DIARY & Invoicing - February 2021 LATEST.xlsx
ABCDEFG
7TeamsMain Task per project scheduleEquipment nr.EquipmentPositionTarget for the dayResults
8Team 1Panel Mounting (Per panel)881-1100ConveyorDMS43
9Team 1NONE881-1100ConveyorDMS1
10Team 1NONE881-1100ConveyorDMS1
11Team 2Panel Mounting (Per panel)881-1100ConveyorDMS43
12Team 2NONE881-1100ConveyorDMS1
13Team 2NONE881-1100ConveyorDMS1
14Team 3Multicore Routing (Per team per day) - 4.65m per hour881-1100ConveyorDMS4045
15Team 3NONE881-1100ConveyorDMS1
16Team 3NONE881-1100ConveyorDMS1
17Team 4Panel Mounting (Per panel)120-3100CrusherJIG Sec42
18Team 4Instrumentation Racking Install (Per team per day) - 1.4m/hour120-3100CrusherJIG Sec121.5
19Team 4Multicore Routing (Per team per day) - 4.65m per hour120-3100CrusherJIG Sec4012
20Team 5Multicore Routing (Per team per day) - 4.65m per hour110-4100ApronJIG Prim4038
21Team 5NONE110-4100ApronJIG Prim1
22Team 5NONE110-4100ApronJIG Prim1
23Team 6Instrumentation Racking Install (Per team per day) - 1.4m/hour1100-4100ApronJIG Prim125
24Team 6Multicore Routing (Per team per day) - 4.65m per hour1100-4100ApronJIG Prim4020
25Team 6NONE1100-4100ApronJIG Prim1
26Team 7JB Mounting4
27Team 7Panel Mounting (Per panel)4
28Team 7NONE1
29Team 8NONE1
30Team 8NONE1
31Team 8NONE1
32Fabrication TeamOn Site Cable Rack Modification (Per team per day) 2,9h/Mod33
33Fabrication TeamNONE1
34Fabrication TeamNONE1
26
Cell Formulas
RangeFormula
F8:F34F8=INDEX(Summery!$E$2:$E$28,MATCH('26'!B8,Summery!$B$2:$B$28,0))
Cells with Data Validation
CellAllowCriteria
B8:B34List=Summery!$B$2:$B$28


Copy of DAILY DIARY & Invoicing - February 2021 LATEST.xlsx
ABCDEF
1No.Task DescriptionTarget Hours per unitMeters or Units per UnitUnit Target for Day26
21Sensors Relocate; Spotface (Existing)0.614 
32Spotfacing Sensors (New)0.421 
43Sensor Mounting0.243 
54Sensor Routing (Conduit piping)0.517 
65JB Mounting240
76JB Termination (Per sensor)0.185 
87Panel Stands Fixing (Provisional)33 
98Panel Transport (Per panel)0.328 
109Panel Mounting (Per panel)248
1110Multicore Routing (Per team per day) - 4.65m per hour404038
1211Multicore Terminations0.517 
1312Power Cable Routing (Per team per day) - 4.65m per hour4040 
1413Power Cable Terminations19 
1514Instrumentation Racking Install (Per team per day) - 1.4m/hour12120
1615On Site Cable Rack Modification (Per team per day) 2,9h/Mod330
1716DB Mounting24 
1817DB Termination0.328 
1918DB Panel Stand (Provisional) 33 
2019DB Panel Stands Fixing (Provisional)33 
2120DB Panel Transport0.328 
2221DB Power Cable Routing4040 
2322DB Power Cable Terminations19 
2423JB Panal Stand Manufacturing24 
2524IMX Panal Stand Manufacturing42 
2625IPDB Panal Stand Manufacturing42 
2726Cherry Picker (Hours)11 
2827NONE1158
Summery
Cell Formulas
RangeFormula
E2:E5,E7:E9,E23,E17:E21,E14,E12E2=8.5/C2
E6,E10,E24:E26E6=8/C6
F2:F28F2=IFERROR(SUMIF('26'!$B$8:$B$34,Summery!B2,INDEX('26'!$G$8:$G$34,MATCH(B2,'26'!$B$8:$B$34,0))),"")
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
I can't see why you're using INDEX and MATCH, they are serving no purpose here and are the reason for the formula failing. The row that was giving you what appeared to be the correct result was more by luck than anything else.
This will do exactly the same (with the correct results) but will show 0 in the rows that are currently blank.
Excel Formula:
=SUMIF('26'!$B$8:$B$34,Summery!B2,'26'!$G$8:$G$34)
 
Solution

wynandbecker

New Member
Joined
Feb 23, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I can't see why you're using INDEX and MATCH, they are serving no purpose here and are the reason for the formula failing. The row that was giving you what appeared to be the correct result was more by luck than anything else.
This will do exactly the same (with the correct results) but will show 0 in the rows that are currently blank.
Excel Formula:
=SUMIF('26'!$B$8:$B$34,Summery!B2,'26'!$G$8:$G$34)
Thank you very much @jasonb75 ! That is much simpler and works perfectly.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,210
Messages
5,640,865
Members
417,174
Latest member
diegomuser

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top