SUMIF, INDEX, MATCH behaving oddly

[wynandbecker]

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
	A	B	C	D	E	F	G
7	Teams	Main Task per project schedule	Equipment nr.	Equipment	Position	Target for the day	Results
8	Team 1	Panel Mounting (Per panel)	881-1100	Conveyor	DMS	4	3
9	Team 1	NONE	881-1100	Conveyor	DMS	1
10	Team 1	NONE	881-1100	Conveyor	DMS	1
11	Team 2	Panel Mounting (Per panel)	881-1100	Conveyor	DMS	4	3
12	Team 2	NONE	881-1100	Conveyor	DMS	1
13	Team 2	NONE	881-1100	Conveyor	DMS	1
14	Team 3	Multicore Routing (Per team per day) - 4.65m per hour	881-1100	Conveyor	DMS	40	45
15	Team 3	NONE	881-1100	Conveyor	DMS	1
16	Team 3	NONE	881-1100	Conveyor	DMS	1
17	Team 4	Panel Mounting (Per panel)	120-3100	Crusher	JIG Sec	4	2
18	Team 4	Instrumentation Racking Install (Per team per day) - 1.4m/hour	120-3100	Crusher	JIG Sec	12	1.5
19	Team 4	Multicore Routing (Per team per day) - 4.65m per hour	120-3100	Crusher	JIG Sec	40	12
20	Team 5	Multicore Routing (Per team per day) - 4.65m per hour	110-4100	Apron	JIG Prim	40	38
21	Team 5	NONE	110-4100	Apron	JIG Prim	1
22	Team 5	NONE	110-4100	Apron	JIG Prim	1
23	Team 6	Instrumentation Racking Install (Per team per day) - 1.4m/hour	1100-4100	Apron	JIG Prim	12	5
24	Team 6	Multicore Routing (Per team per day) - 4.65m per hour	1100-4100	Apron	JIG Prim	40	20
25	Team 6	NONE	1100-4100	Apron	JIG Prim	1
26	Team 7	JB Mounting				4
27	Team 7	Panel Mounting (Per panel)				4
28	Team 7	NONE				1
29	Team 8	NONE				1
30	Team 8	NONE				1
31	Team 8	NONE				1
32	Fabrication Team	On Site Cable Rack Modification (Per team per day) 2,9h/Mod				3	3
33	Fabrication Team	NONE				1
34	Fabrication Team	NONE				1
26

Cell Formulas
Range	Formula
F8:F34	F8	=INDEX(Summery!$E$2:$E$28,MATCH('26'!B8,Summery!$B$2:$B$28,0))

Cells with Data Validation
Cell	Allow	Criteria
B8:B34	List	=Summery!$B$2:$B$28

Copy of DAILY DIARY & Invoicing - February 2021 LATEST.xlsx
	A	B	C	D	E	F
1	No.	Task Description	Target Hours per unit	Meters or Units per Unit	Unit Target for Day	26
2	1	Sensors Relocate; Spotface (Existing)	0.6		14
3	2	Spotfacing Sensors (New)	0.4		21
4	3	Sensor Mounting	0.2		43
5	4	Sensor Routing (Conduit piping)	0.5		17
6	5	JB Mounting	2		4	0
7	6	JB Termination (Per sensor)	0.1		85
8	7	Panel Stands Fixing (Provisional)	3		3
9	8	Panel Transport (Per panel)	0.3		28
10	9	Panel Mounting (Per panel)	2		4	8
11	10	Multicore Routing (Per team per day) - 4.65m per hour		40	40	38
12	11	Multicore Terminations	0.5		17
13	12	Power Cable Routing (Per team per day) - 4.65m per hour		40	40
14	13	Power Cable Terminations	1		9
15	14	Instrumentation Racking Install (Per team per day) - 1.4m/hour		12	12	0
16	15	On Site Cable Rack Modification (Per team per day) 2,9h/Mod		3	3	0
17	16	DB Mounting	2		4
18	17	DB Termination	0.3		28
19	18	DB Panel Stand (Provisional)	3		3
20	19	DB Panel Stands Fixing (Provisional)	3		3
21	20	DB Panel Transport	0.3		28
22	21	DB Power Cable Routing		40	40
23	22	DB Power Cable Terminations	1		9
24	23	JB Panal Stand Manufacturing	2		4
25	24	IMX Panal Stand Manufacturing	4		2
26	25	IPDB Panal Stand Manufacturing	4		2
27	26	Cherry Picker (Hours)	1		1
28	27	NONE	1		1	58
Summery

Cell Formulas
Range	Formula
E2:E5,E7:E9,E23,E17:E21,E14,E12	E2	=8.5/C2
E6,E10,E24:E26	E6	=8/C6
F2:F28	F2	=IFERROR(SUMIF('26'!$B$8:$B$34,Summery!B2,INDEX('26'!$G$8:$G$34,MATCH(B2,'26'!$B$8:$B$34,0))),"")

 

[jasonb75]

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.

=SUMIF('26'!$B$8:$B$34,Summery!B2,'26'!$G$8:$G$34)

 

[wynandbecker]

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.

=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.