Count specific text in specific cells?

[Geek Girl 007]

ROTAS.xlsx
	A	B	C	D
1		Day:	Sun	Mon
2		Date:	29-Dec	30-Dec
3	HOBBA	Name 1		RD
4	Dep 1	Name 2		OFF
5
6
7
8
9	Dep 2	Name 3
10
11
12	Dep 3	Name 4		OFF
13
14
15	Dep 4	Name 5
16
17
18		Total Managers off:
19		Total Staff off:
Sheet6

Cells with Data Validation
Cell	Allow	Criteria
C3:D3	List	=LIST_one
C4:D17	List	=List_two

I want row 18 to add up rows 3, 4, 9, 12 and 15 only if they contain the text 'RD' or 'OFF'
Can this be done??

 

[Joe4]

Do you mean something like this?

Formula in cell C18 (and then copied to cell D18):

=SUM(COUNTIFS($B3:$B17,"<>",C3:C17,{"RD","OFF"}))

 

[Geek Girl 007]

Do you mean something like this?
View attachment 120995

Formula in cell C18 (and then copied to cell D18):

=SUM(COUNTIFS($B3:$B17,"<>",C3:C17,{"RD","OFF"}))

This is fab, I thought it would be easier because I wanted to do the same formula in all the other cells for Total staff off.
Eventually there will be names in all the boxes in column B, will this still work then??

 

[Joe4]

This is fab, I thought it would be easier because I wanted to do the same formula in all the other cells for Total staff off.
Eventually there will be names in all the boxes in column B, will this still work then??

It checks every row for a value in column B, and one of those codes in the other columns.
If both conditions are met, it returns that row in the count.

 

[Geek Girl 007]

It checks every row for a value in column B, and one of those codes in the other columns.
If both conditions are met, it returns that row in the count.

I thought so, then no, this doesn't work for my needs, I'm sorry, I should have been clearer.
Eventually, I will have the names of the manager at the top of each section, I want to be able to add up how many managers are off and how many staff are off as in rows 18 and 19.....?

ROTAS.xlsx
	A	B	C	D
1		Day:	Sun	Mon
2		Date:	29-Dec	30-Dec
3	Head	Manager 1		RD
4	Dep 1	Manager 2		OFF
5		Staff 1
6		Staff 2		OFF
7		Staff 3
8		Staff 4
9	Dep 2	Manager 3
10		Staff 1		OFF
11		Staff 2
12	Dep 3	Manager 4		OFF
13		Staff 1
14		Staff 2
15	Dep 4	Manager 5
16		Staff 1		OFF
17		Staff 2
18		Total Managers off:
19		Total Staff off:
Sheet6

Cells with Data Validation
Cell	Allow	Criteria
C3:D3	List	=LIST_one
C4:D17	List	=List_two

 

[Cubist]

Try:

Book1
	A	B	C	D
1		Day:	Sun	Mon
2		Date:	12/29/2025	12/30/2025
3	Head	Manager 1		RD
4	Dep 1	Manager 2		OFF
5		Staff 1
6		Staff 2		OFF
7		Staff 3
8		Staff 4
9	Dep 2	Manager 3
10		Staff 1		OFF
11		Staff 2
12	Dep 3	Manager 4		OFF
13		Staff 1
14		Staff 2
15	Dep 4	Manager 5
16		Staff 1		OFF
17		Staff 2
18		Total Managers off:		3
19		Total Staff off:		3
Sheet1

Cell Formulas
Range	Formula
D18	D18	=SUM((A3:A17<>"")*((D3:D17="RD")+(D3:D17="OFF")))
D19	D19	=SUM((A3:A17="")*((D3:D17="RD")+(D3:D17="OFF")))

 

[Geek Girl 007]

Try:

Book1
	A	B	C	D
1		Day:	Sun	Mon
2		Date:	12/29/2025	12/30/2025
3	Head	Manager 1		RD
4	Dep 1	Manager 2		OFF
5		Staff 1
6		Staff 2		OFF
7		Staff 3
8		Staff 4
9	Dep 2	Manager 3
10		Staff 1		OFF
11		Staff 2
12	Dep 3	Manager 4		OFF
13		Staff 1
14		Staff 2
15	Dep 4	Manager 5
16		Staff 1		OFF
17		Staff 2
18		Total Managers off:		3
19		Total Staff off:		3
Sheet1

Cell Formulas
Range	Formula
D18	D18	=SUM((A3:A17<>"")*((D3:D17="RD")+(D3:D17="OFF")))
D19	D19	=SUM((A3:A17="")*((D3:D17="RD")+(D3:D17="OFF")))

Thank you, I will have a play and get back to you, this looks great though.....

 

[Geek Girl 007]

Thank you, I will have a play and get back to you, this looks great though.....

Perfect, Thank you

 

[Joe4]

I thought so, then no, this doesn't work for my needs, I'm sorry, I should have been clearer.
Eventually, I will have the names of the manager at the top of each section, I want to be able to add up how many managers are off and how many staff are off as in rows 18 and 19.....?

ROTAS.xlsx
	A	B	C	D
1		Day:	Sun	Mon
2		Date:	29-Dec	30-Dec
3	Head	Manager 1		RD
4	Dep 1	Manager 2		OFF
5		Staff 1
6		Staff 2		OFF
7		Staff 3
8		Staff 4
9	Dep 2	Manager 3
10		Staff 1		OFF
11		Staff 2
12	Dep 3	Manager 4		OFF
13		Staff 1
14		Staff 2
15	Dep 4	Manager 5
16		Staff 1		OFF
17		Staff 2
18		Total Managers off:
19		Total Staff off:
Sheet6

Cells with Data Validation
Cell	Allow	Criteria
C3:D3	List	=LIST_one
C4:D17	List	=List_two

Yes, sorry that wasn't quite clear from the original question.
I am glad that Cubist was able to find a solution that works for you.

 

[Joe4]

Also note that my original formula should also work, if you adjust it slightly to check column A and not B, i.e.

=SUM(COUNTIFS($A3:$A17,"<>",D3:D17,{"RD","OFF"}))