Look Up with Sum

[Darren Blake]

Ok here goes, I have a spreadsheet which calculates each day the amount of work a colleague manages to achieve, however on some days the individual may not appear and my aim is to look up each day and sum the weekly amount for that person (e.g. AC, SG). My data is taken from pivot tables I have created but not matter what I use I can't the formula to look at Monday find the person, take the qauntity against that person (SUM of Cases) then look at Tuesday, Wednesday etc add them all together and return the result. I have tried to use SUMPRODUCT, SUMIFS, without success

HELP PLEASE

Monday							Tuesday							Wednesday							Thursday							Friday
	SUM of Cases	SUM of	Case Pick	Actual Pick Rate PH	Percentage			SUM of Cases	SUM of	Case Pick	Actual Pick Rate PH	Percentage			SUM of Cases	SUM of	Case Pick	Actual Pick Rate PH	Percentage			SUM of Cases	SUM of	Case Pick	Actual Pick Rate PH	Percentage			SUM of Cases	SUM of	Case Pick	Actual Pick Rate PH	Percentage
	0	0						0	0						0	0						0	0						0	0
AC	20	6	100	200.0	200.00%		AC	10	1	100	600.0	600.00%		AC	10	1	100	600.0	600.00%		SG	10	60	100	10.0	10.00%					100	0.0	0.00%
SG	10	4	100	150.0	150.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%
			100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%
			100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%
			100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%
			100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%
			100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%
			100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%
			100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%
			100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%
			100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%					100	0.0	0.00%

<colgroup><col style="width: 49px"><col width="53"><col width="52"><col width="41"><col width="74"><col width="82"><col width="9"><col width="51"><col width="52"><col width="56"><col width="53"><col width="82"><col width="80"><col width="9"><col width="50"><col width="59"><col width="62"><col width="52"><col width="83"><col width="82"><col width="10"><col width="51"><col width="64"><col width="59"><col width="57"><col width="78"><col width="79"><col width="11"><col width="45"><col width="55"><col width="54"><col width="49"><col width="81"><col width="84"></colgroup><tbody>
</tbody>

 

[DILIPandey]

Hi Darren,

My suggestion would be to achieve this from the data source only i.e., the tabular data on which pivot is created.
Can you post a small portion of that tabular data here ? Thanks.

Regards,
DILIPandey

 

[oldbrewer]

joe	20	6	100	200	200.00%		joe	10	1	100	600	600.00%		joe	11	1	100	600	600.00%		fred	12	60	100	10	10.00%
fred	10	4	100	150	150.00%					100	0	0.00%					100	0	0.00%					100	0	0.00%
	mon	tue	wed	thu	fri
joe	20	10	11	#N/A	#N/A
fred	10	#N/A	#N/A	12	#N/A
	this lower table uses offset match
	to pull the info
	assumes names of workers will always be
	in columns A,H,O,V,AC

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>