count overtime hours in a calendar

[ttowncorp]

Im trying to figure out how to count overtime hours in a calendar year with a basic countif forumal if possible. I'm tracking hours by initals of employees. for example John Smith (JS) worked 3 hours OT on Thursday. So on Thursday i type in LS=3 for my reference. OT ranges from 1-8 in a day. and i want to count all the hours that person did in that month. for example

Monday 1st	Tuesday 2nd	Wednesday3rd	Thursday 4th	Friday 5th	Saturday 6th	Sunday 7th
JS=3	JS=7	JS=2	JS=1	JS=3.5	JS=.5	JS=4
GB=1	GB=2	GB=4	GB=2	GB=2	GB=3	GB=3

 

[Jeffrey Mahoney]

Please try this:

Book1
	A	B	C	D	E	F	G	H	I
1	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
2	JS=3	JS=7	JS=2	JS=1	JS=3.5	JS=.5	JS=4		21
3	GB=1	GB=2	GB=4	GB=2	GB=2	GB=3	GB=3		17
Sheet2

Cell Formulas
Range	Formula
I2:I3	I2	=SUM(--TEXTAFTER(A2:G2,"="))

 

[ttowncorp]

Please try this:

Book1
	A	B	C	D	E	F	G	H	I
1	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
2	JS=3	JS=7	JS=2	JS=1	JS=3.5	JS=.5	JS=4		21
3	GB=1	GB=2	GB=4	GB=2	GB=2	GB=3	GB=3		17
Sheet2

Cell Formulas
Range	Formula
I2:I3	I2	=SUM(--TEXTAFTER(A2:G2,"="))

Thanks. how do i get this to work if my data is on a separate sheet? I have sheets from January thru December and my totals will be on a sheet called total.

 

[Jeffrey Mahoney]

I did you one better, These formulas will sum the values based on the Person Initials also

Book1
	I	J
1	Initials	Sum
2	JS	21
3	GB	17
Total

Cell Formulas
Range	Formula
J2:J3	J2	=SUM(TOROW(LEFT(Sheet3!$A$2:$G$3,2)=I2)*(--TEXTAFTER(TOROW(Sheet3!$A$2:$G$3),"=")))

Book1
	A	B	C	D	E	F	G
1	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
2	JS=3	JS=7	JS=2	JS=1	JS=3.5	JS=.5	JS=4
3	GB=1	GB=2	GB=4	GB=2	GB=2	GB=3	GB=3
Sheet3

 

[ttowncorp]

I did you one better, These formulas will sum the values based on the Person Initials also

Book1
	I	J
1	Initials	Sum
2	JS	21
3	GB	17
Total

Cell Formulas
Range	Formula
J2:J3	J2	=SUM(TOROW(LEFT(Sheet3!$A$2:$G$3,2)=I2)*(--TEXTAFTER(TOROW(Sheet3!$A$2:$G$3),"=")))

Book1
	A	B	C	D	E	F	G
1	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
2	JS=3	JS=7	JS=2	JS=1	JS=3.5	JS=.5	JS=4
3	GB=1	GB=2	GB=4	GB=2	GB=2	GB=3	GB=3
Sheet3

thank you. this works perfectly.