Index/Match/Small

[AnotherManipulator]

Hello! I've been a lurker for a while and can usually find what I need, but I needed the personal touch with this one so hope someone can help.

Pretty new to Index/Match formula, and even newer to Small.

To outline what I need, I want to show total hours worked for my guys over all the jobs they do.

I have a list of Staff members, and their corresponding hours per job. Naturally, they will be doing more than one job over the period I'm reporting for so I need to add these up.

Sometimes the job requires more than one person, so this is how I have organised the data:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Job No[/TD]
[TD]Emp1[/TD]
[TD]Emp2[/TD]
[TD]Emp3[/TD]
[TD]Emp4[/TD]
[TD][/TD]
[TD]Emp1 Hrs[/TD]
[TD]Emp2 Hrs[/TD]
[TD]Emp3 Hrs[/TD]
[TD]Emp4 Hrs[/TD]
[TD]Total Hrs on Job[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]Dave[/TD]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]0002[/TD]
[TD]Dave[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]0003[/TD]
[TD]Pete[/TD]
[TD]Dave[/TD]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]


So working out total hours is easy enough, and looking at the above I can see that Dave has worked 11 hours, but how can I help Excel see that?

Hope I've explained that right.

Many thanks in advance

 

[FormR]

Re: Index/Match/Small Help

Hi, welcome to the board!

Here is one way, for example:

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Job No	Emp1	Emp2	Emp3	Emp4		Emp1 Hrs	Emp2 Hrs	Emp3 Hrs	Emp4 Hrs		Dave	11
2	1	Dave	John				2	3
3	2	Dave					4
4	3	Pete	Dave	John			3	5	2
Sheet1

Cell Formulas
Range	Formula
M1	=SUMIF(B2:E4,L1,G2:J4)

 

[AnotherManipulator]

Re: Index/Match/Small Help

Thank you. Oft in life the simpler solution is best. I knew I was over-complicating things!

Many thanks for your amazingly quick response!