Formula

[Bob1982]

Hello

I'm new here so hopefully I have posted this in the correct place. I am currently making a rota at work and want to count how many weekends employees are working. Is there a formula which identifies certain cells containing a particular text and making that equal the value of 1 which I can add together with other cells for that year, giving me the amount of weekends worked.

For example:

(if H6 or I6 contains a D or an N that equals 1) + (if O6 or P6 contains a D or an N that equals 1) = the amount of weekends worked if D or N are mentioned in those cells.


I don't know if that make sense or not, or I could just be other thinking it and its really simple. I don't know, but any help is really appreciated.

 

[etaf]

various ways to do that
the way you suggest

I would use
countif(C6:ZZ6, "D") + countif(C6:ZZ6, "N")

I'll put something together to show

change the range to your full year

 

[etaf]

Book11
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	names	Tue 01/02/2022	Wed 02/02/2022	Thu 03/02/2022	Fri 04/02/2022	Sat 05/02/2022	Sun 06/02/2022	Mon 07/02/2022	Tue 08/02/2022	Wed 09/02/2022	Thu 10/02/2022	Fri 11/02/2022	Sat 12/02/2022	Sun 13/02/2022	Mon 14/02/2022	Tue 15/02/2022	Count N & D's	formula usimg just the dates	networkdays
2
3
4
5
6						N	N						D	D			4	4	4
Sheet1

Cell Formulas
Range	Formula
C1:P1	C1	=B1+1
Q6	Q6	=COUNTIF(B6:P6,"D")+COUNTIF(B6:P6,"N")
R6	R6	=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(B1&":"&P1)))>=6))
S6	S6	=NETWORKDAYS.INTL(B1,P1,"1111100")

there are ways just to count using the dates and not the codes , if needed , BUT i assume the codes are used to may an person working

 

[Snakehips]

I'm thinking that an individual's weekend count will be +1 for the Saturday, or the Sunday or both, of the the same weekend?

In that case then, give or take any need to adjust for actual ranges, maybe like.....

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2			Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue
3
4
5	Name	Weekends	01-Aug	02-Aug	03-Aug	04-Aug	05-Aug	06-Aug	07-Aug	08-Aug	09-Aug	10-Aug	11-Aug	12-Aug	13-Aug	14-Aug	15-Aug	16-Aug
6	Employee 1	1				D	D						D	D	N	N	N
7	Employee 2	1		D	D			N			D	D					N
8	Employee 3	2					D	D	D					N	N	N	N
9	Employee 4	2						D	D						N		N
10	Employee 5	2						D								N	N
11	Employee 6	0			D	D	D					D	D				N
12
Sheet6

Cell Formulas
Range	Formula
C2:R2	C2	=TEXT(C5,"ddd")
D5:R5	D5	=C5+1
B6:B11	B6	=SUMPRODUCT((C6:ZY6>"")*(WEEKDAY($C$5:$ZY$5,2)>5))-SUMPRODUCT((C6:ZY6>"")*(D6:ZZ6>"")*(WEEKDAY($C$5:$ZY$5,2)=6)*(WEEKDAY($D$5:$ZZ$5,2)=7))