SUM of COUNTIFS with OR logic Error

[bisho]

Hi,

I'm trying to change the criteria between the brackets {} in A42 formula to have the cell reference C2, C3 & C4 instead of using the text in these 3 cells (=SUM(COUNTIFS(A8:A39,{C2,C3,C4}))) but I keep getting this error.
Isn't allowed to use cell references in this scenario?

Thanks,

Room Attendant Schedule 2024.xlsx
	A	B	C
1	Sunday		Shifts
2	28-Apr		8AM-4:30PM
3	44.00%		9AM-5:30PM
4	129		10AM-6:30PM
5	48		4PM-10PM
6	53		R/OFF
7			OFF
8	OFF		ON CALL
9	ON CALL
10	OFF
11	8AM-4:30PM
12	OFF
13	10AM-6:30PM
14	OFF
15	9AM-5:30PM
16	OFF
17	R/OFF
18	10AM-6:30PM
19	8AM-4:30PM
20	10AM-6:30PM
21	R/OFF
22	10AM-6:30PM
23	10AM-6:30PM
24	R/OFF
25	8AM-4:30PM
26	4PM-10PM
27	10AM-6:30PM
28	8AM-4:30PM
29	10AM-6:30PM
30	9AM-5:30PM
31	R/OFF
32	ON CALL
33	ON CALL
34	10AM-6:30PM
35	4PM-10PM
36	10AM-6:30PM
37	9AM-5:30PM
38	10AM-6:30PM
39	OFF
40
41
42	17
4.28.2024-5.4.2024

Cell Formulas
Range	Formula
A1	A1	=TEXT(A2,"dddd")
A42	A42	=SUM(COUNTIFS(A8:A39,{"8AM-4:30PM","9AM-5:30PM","10AM-6:30PM"}))

Cells with Data Validation
Cell	Allow	Criteria
A8:A39	List	=$C$2:$C$8

 

[NickFru]

Hello - if these times are fixed ie,

8AM-4:30PM

9AM-5:30PM

10AM-6:30PM You could list these 3 dates and set these up under a helper column and then enter the formula; =SUM(COUNTIFS(A:A Being Criteria Text Range, Then Range Above Being Criteria (Say C1:C3) Once you start to add more of the above dates to the text range, this should update the count. Hopefully this works for you.

 

[Fluff]

Don't put the cells inside {} just use

=SUM(COUNTIFS(A8:A39,C2:C4))

 

[bisho]

Don't put the cells inside {} just use

=SUM(COUNTIFS(A8:A39,C2:C4))

Thank you very much. This formula was what I wanted.

 

[bisho]

Hello - if these times are fixed ie,

Thank you for your input.

 

[Fluff]

Glad we could help & thanks for the feedback.