Formula to summarize

[rizmomin]

Hello:

Please refer to attached sheet.

I have Sheet1 and Sheet2

Sheet1, i have date in cell A3:A33 and employee name in cell C2:D2

Sheet2, has clockIn-Out for employees and data is in sheet2 cell O65:Z68.
Red Highled fields are the calculated hours worked.

I want to summary the total hours each employee worked for a given date in Sheet1.

Please help me with the formula to give the result.

Thanks

Riz

Book2.xlsx

[rizmomin]

Hello:

i have added calculated hours worked for each employee for each day manually in Shee1 Cell C3:E5.
Let me know if you have any questions.
Thanks
Riz

[FDibbins]

Your data layout could be ajusted to make the summary easier...

	O	P	Q	R	S	T	U	V
57
58		KIRSTEN				HALEY
59	7/1/2014	1000	1600	6		1000	1700	7
60	7/1/2014	1800	2100	3				0
61	7/2/2014	900	1500	6		1100	1400	3
62	7/2/2014			0		1800	2100	3
63	7/3/2014	1200	1700	5		1300	1800	5
64	7/3/2014			0		1800	2200	4

Then in sheet1 C3, use this, copied down...
=SUMIF(Sheet2!$O$59:$O$64,Sheet1!$A3,Sheet2!$R$59:$R$64)
and D3, copied down...
=SUMIF(Sheet2!$O$59:$O$64,Sheet1!$A3,Sheet2!$V$59:$V$64)

[rizmomin]

hi Ford:

I could not change the lay out since they are imported from Cash Register just like i have shown in the sheet.

Riz

[FDibbins]

ok so what would the data look like over, say, a full 2 or 3 week period?

[rizmomin]

Hi Ford:

It would be for the whole month..
Riz

[rizmomin]

Hi Ford:

I am using the similar formula in one of the other sheet without any problem.
I am attaching 2 jpg file for you to see the formula.
Please help.
Thanks
Riz

[rizmomin]

Hello:

Please find attached the formula i am using from working sheet but does not work on this.
Help to solve the problem.
Thanks
Riz

[FDibbins]

ok so what would the data look like over, say, a full 2 or 3 week period?

I actually wanted to see a sample of what your data would look like over a 2 or 3 (preferably) week period. I want this so I can see what options we have for a formula

[rizmomin]

Hi ford:
I think jpg files I uploaded should show you the formula on different sheet.
Let me know if any questions.

Riz

[FDibbins]

Please upload a sample of your workbook, not a picture of your data.
Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff)

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

[rizmomin]

Hello Ford:

Please refer to attached file.

The data i need populated by the formula is in sheet "EmpINOut" cell M39:P69.
This data i will use in the same sheet on the top part of the screen which will show me weely hours for each employee.
Please look at the formula which i am using with other sheet without any problem.
This one give #N/A error.

Please let me know if you have any further questions.

Riz

[oeldere]

My advice would be, to get all data in a seperated sheet in a table format.

date / name / value.

After that you can use a pivot table, to analyze your data.

[rizmomin]

Hello Guys:

Hello:

I have simplified to understand the sheet.
Have ClockIn-Out data f employees in Sheet "Daily_Receipt", cell O66:R70, Column O has ClockIn, Column P Clock Out and Column R has total hours worked.
I need a formula to bring total hours worked for employee in sheet "EmpInOut" sheet in row 37,M37:P37.
I have manually entered the values in cell M37:P37.

Please Let me know if you have any questions.
Thanks
Riz

[FDibbins]

@ oeldere, read post # 4

[rizmomin]

Hi Guys :

Thank you so much for great help.
Below is the formula which now worked for me without any problem.

Please Login or Register  to view this content.

Once again thank u for help.
Riz

[FDibbins]

OK try this, copied down and across....

=SUMIF(OFFSET(Daily_Receipt!$O$65,1,MATCH(EmpINOut!$A39,Daily_Receipt!$O$65:$AL$65,0)-1,50,1),M$38,OFFSET(Daily_Receipt!$O$65,1,MATCH(EmpINOut!$A39,Daily_Receipt!$O$65:$AL$65,0)+2,50,1))

edit: good work coming up with that