Formula to count number of weekends within a rota.

[Browneh89]

Hello,

I would appreciate someone's help. I have a rota set out like the one below and I'm wondering if the is any variation of a count formula which would be able to allow me to count the number of weekend days each individual staff member has worked?

bob.xlsx
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
8																		Example Results
9																		SATURDAYS WORKED	SUNDAYS WORKED
10																	BOB	2	1
11		SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY	SUNDAY	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY		TIM	0	1
12		28/04/2021	29/04/2021	30/04/2021	01/05/2021	02/05/2021	03/05/2021	04/05/2021	05/05/2021	06/05/2021	07/05/2021	08/05/2021	09/05/2021	10/05/2021	11/05/2021
13
14	BOB	9am - 5pm	9am - 5pm	9am - 5pm			9am - 5pm	9am - 5pm			9am - 5pm	9am - 5pm	9am - 5pm	9am - 5pm	9am - 5pm
15
16	TIM		9am - 5pm	9am - 5pm	9am - 5pm	9am - 5pm	9am - 5pm		9am - 5pm	9am - 5pm	9am - 5pm	9am - 5pm	9am - 5pm
Sheet1

 

[jasonb75]

How about

Book1
	T	U	V
8		Example Results
9		SATURDAY	SUNDAY
10	BOB	2	1
11	TIM	0	1
Sheet5

Cell Formulas
Range	Formula
U10:V11	U10	=COUNTIFS($E$11:$R$11,U$9,INDEX($E$14:$R$16,MATCH($T10,$D$14:$D$16,0),0),"<>")

 

[Eric W]

Another way:

=SUMPRODUCT(($E$13:$R$16<>"")*($D$13:$D$16=$T10)*($E$11:$R$11=U$9))

Although I suspect Jason's would be a bit faster on large ranges.

 

[Browneh89]

How about

Book1
	T	U	V
8		Example Results
9		SATURDAY	SUNDAY
10	BOB	2	1
11	TIM	0	1
Sheet5

Cell Formulas
Range	Formula
U10:V11	U10	=COUNTIFS($E$11:$R$11,U$9,INDEX($E$14:$R$16,MATCH($T10,$D$14:$D$16,0),0),"<>")

Thank you this worked perfectly!!

 

[Browneh89]

Another way:

=SUMPRODUCT(($E$13:$R$16<>"")*($D$13:$D$16=$T10)*($E$11:$R$11=U$9))

Although I suspect Jason's would be a bit faster on large ranges.

Thanks, mate, unfortunately, this would be using a massive range!

 

[jasonb75]

If you want an efficient formula for a large range then I would suggest deleting the empty row (15) and moving the results down a few rows.

Book1
	T	U	V
11		SATURDAY	SUNDAY
12
13
14	BOB	2	1
15	TIM	0	1
Sheet5

Cell Formulas
Range	Formula
T14:T15	T14	=D14
U14:U15	U14	=COUNTA(K14,R14)
V14:V15	V14	=COUNTA(E14,L14)

 

[Browneh89]

If you want an efficient formula for a large range then I would suggest deleting the empty row (15) and moving the results down a few rows.

Book1
	T	U	V
11		SATURDAY	SUNDAY
12
13
14	BOB	2	1
15	TIM	0	1
Sheet5

Cell Formulas
Range	Formula
T14:T15	T14	=D14
U14:U15	U14	=COUNTA(K14,R14)
V14:V15	V14	=COUNTA(E14,L14)

Thank you for your advice! however, those rows are needed for the sheet. What I posted on here was just a dummy sheet to represent the one I had to work on but does not contain everything within the actual sheet I am working upon.

 

[jasonb75]

I had suspected that might be the case but it was worth mentioning on the off chance that it might work.