how much annual leave taken

[Rogerisit]

Hello!
I am trying to calculate how much annual leave "Bob" had during the week ending 6/1/2017. This sheet hasn't pasted so well, sorry. The "a" on the attendance sheet sits in A7. But I need to know for each entire week, and this formula needs to work for all employees. I'm stuck!! Thank you for any help

January 2017

Present

A

Annual Leave

S

Sick Leave

U

Unpaid Leave

W

Weekend worked

Sun 01

Mon 02

Tue 03

Wed 04

Thu 05

Fri 06

Sat 07

Sun 08

Mon 09

Tue 10

Wed 11

Thu 12

Fri 13

Sat 14

Sun 15

Mon 16

Tue 17

Wed 18

Thu 19

Fri 20

Sat 21

Sun 22

Mon 23

Tue 24

Wed 25

Thu 26

Fri 27

Sat 28

Sun 29

Mon 30

Tue 31

Bob

s

s

A

p

p

Jim

Frank

Arthur

Mary

Doris

Betty

Nancy

Charles

Annie

<colgroup><col span="2"><col span="31"></colgroup><tbody>
</tbody>

<colgroup><col span="2"><col span="31"></colgroup><tbody>
</tbody>

 

[Eric W]

I took the liberty of rearranging your sheet a little. If this isn't what you had in mind, let me know, and I'll try to adapt.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	January 2017
2		These totals are for the week ending:			1/13/2017
3		P	A	S	U	W
4		Present	Annual Leave	Sick Leave	Unpaid Leave	Weekend worked
5							1/1/2017	1/2/2017	1/3/2017	1/4/2017	1/5/2017	1/6/2017	1/7/2017	1/8/2017	1/9/2017	1/10/2017	1/11/2017	1/12/2017	1/13/2017
6	Bob	5	0	0	0	0		s	s	a	p	p			p	p	p	p	p
7	Jim	3	0	0	0	2		p	p	p	p	p	w	w	p	p	p
8	Frank	3	2	0	0	0		p	p	p	p	a			a	a	p	p	p
9	Arthur	0	0	5	0	0		a	a	a	a	a			s	s	s	s	s
10	Mary	3	0	2	0	0		s	s	s	s	p			p	s	p	s	p
11	Doris	5	0	0	0	0	w	p	p	p	p	p			p	p	p	p	p
12	Betty	0	0	0	5	0		a	p	p	p	p			u	u	u	u	u
13	Nancy	3	0	2	0	0		p	p	s	p	p			p	p	p	s	s
14	Charles	4	0	0	0	0		p	p	p	p	a			p	p	p	p
15	Annie	4	1	0	0	0		p	p	p	p	u			a	p	p	p	p

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet10​

Worksheet Formulas Cell Formula B6 =COUNTIFS($G$5:$AK$5,"<="&$E$2,$G$5:$AK$5,">="&$E$2-6,$G6:$AK6,B$3) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The five values you had listed are in B3:F3, with the description below. The week you are interested in will be in E2. The list of dates is for January, which goes to column AK. I assume you'd want to carry that out to Dec 31, but I don't show all that. The dates in E2 and in row 5 are all Excel dates. Then you can enter the formula in B6, and copy it to the right to F6, and down as far as necessary. The totals will all adjust when you change the value in E2.

Let me know if this helps.

 

[Rogerisit]

Thank you.
I have copy and pasted your formula (and updated cell references) I keep getting an error #name?. Any thoughts?
I really appreciate your help.

 

[Eric W]

Could you paste what your formula looks like now, after adjusting it for your references?

 

[Rogerisit]

No point. I have learned that excel 2003 doesn't support countifs!! I'm looking forward to an upgrade one day. Apparently sumproduct can help fill this void. I'd gladly accept a sumproduct formula! Thanks for your help Eric

 

[Eric W]

The SUMPRODUCT equivalent is:

B6: =SUMPRODUCT(--($G$5:$AK$5<=$E$2),--($G$5:$AK$5>=$E$2-6),--($G6:$AK6=B$3))

This works in my copy of Excel 2000, so it should work in 2003. Of course, Excel 2003 only allows 256 columns per sheet, so you can't put the entire year on one sheet. One month per sheet, plus probably a few extra days of the next month so you get a full week in.

Good luck!

 

[Rogerisit]

Thanks Eric. Yes, I discovered that as I first created 3 months and then tried to get the formula to work. I have turned it vertical, but have been following your formula and then just changing the cell references, ie date range. Stupid limitations!! I have entered this formula you have given me and it is only giving me the number of "a" (B3) in Jan 6 box, which of course is 1. My formula looks like this: =SUMPRODUCT(--(Attendance!A6:A381<=Bob!A5),--(Attendance!A6:A381>=Bob!A5),--(Attendance!B6:B381=Attendance!$D$2))

 

[Rogerisit]

I missed the "-6". Thanks so much. REALLY appreciate your help

 

[Rogerisit]

Now I have another problem: half days. So Bob has half annual leave, half work day. a/p or p/a. Clearly I have no idea what I'm doing as =SUMPRODUCT(--(Attendance!A6:A381<=Bob!A5),--(Attendance!A6:A381>=Bob!A5-6),--(Attendance!B6:B381=Attendance!$D$2),--(Attendance!B6:B381="a/"?""),----(Attendance!B6:B381=""?"/a")) Doesn't work! Any thoughts on that one? These thing just snowball.

 

[Rogerisit]

I've managed to spread it out and can get it to work, except the ""?"/a" thing doesn't work