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:

Job No	Emp1	Emp2	Emp3	Emp4		Emp1 Hrs	Emp2 Hrs	Emp3 Hrs	Emp4 Hrs	Total Hrs on Job
0001	Dave	John				2	3			5
0002	Dave					4				4
0003	Pete	Dave	John			3	5	2		10

<tbody>
</tbody>

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!