countifs if criteria is not alligned with criteria range

[ExcelNewbie2020]

how to count a criteria if its not alligned with criteria range. Please see below table. need to count the hours between 2 dates (from/to). however, dates are not alligned with the hours.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		EXPECTED RESULT
2		FROM	TO
3	Name	11-08-23	14-08-23	11-08-23			12-08-23			13-08-23			14-08-23
4		Count of 12 hrs	Count of 8hrs	Department	Shift	HOURS	Department	Shift	HOURS	Department	Shift	HOURS	Department	Shift	HOURS
5	name1	0	4	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8
6	name2	0	4	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8
7	name3	4	0	ABC	shift 3	12	ABC	shift 3	12	ABC	shift 3	12	ABC	shift 3	12
8	name4	0	0	DEF	shift 1	0	DEF	shift 1	0	DEF	shift 1	0	DEF	shift 1	0
9	name5	0	4	ABC	shift 2	8	ABC	shift 2	8	ABC	shift 2	8	ABC	shift 2	8
10	name6	4	0	DEF	shift 3	12	DEF	shift 3	12	DEF	shift 3	12	DEF	shift 3	12
11	name7	0	4	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8
12	name8	0	4	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8
13	name9	0	4	ABC	shift 3	8	ABC	shift 3	8	ABC	shift 3	8	ABC	shift 3	8
14	name10	4	0	DEF	shift 1	12	DEF	shift 1	12	DEF	shift 1	12	DEF	shift 1	12
15	name11	4	0	ABC	shift 2	12	ABC	shift 2	12	ABC	shift 2	12	ABC	shift 2	12
16	name12	0	4	DEF	shift 3	8	DEF	shift 3	8	DEF	shift 3	8	DEF	shift 3	8
17	name13	0	0	EFG	OFF	0	EFG	OFF	0	EFG	OFF	0	EFG	OFF	0
Sheet1

 

[ExcelNewbie2020]

i figure it out using offset function.. thanks

 

[Peter_SSs]

Good to hear you figured it out.
If you would like to post the actual solution then it is perfectly fine to mark that post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[Peter_SSs]

BTW, as I understand it you can achieve what you want without the volatile function OFFSET
You may have to adjust the ranges to suit your actual data, but this non-volatile formula produces the expected results for that sample data.

23 08 14.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	EXPECTED RESULT
2	FROM	TO
3	11/08/23	14/08/23	11/08/23			12/08/23			13/08/23			14/08/23
4	Count of 12 hrs	Count of 8hrs	Department	Shift	HOURS	Department	Shift	HOURS	Department	Shift	HOURS	Department	Shift	HOURS
5	0	4	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8
6	0	4	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8
7	4	0	ABC	shift 3	12	ABC	shift 3	12	ABC	shift 3	12	ABC	shift 3	12
8	0	0	DEF	shift 1	0	DEF	shift 1	0	DEF	shift 1	0	DEF	shift 1	0
9	0	4	ABC	shift 2	8	ABC	shift 2	8	ABC	shift 2	8	ABC	shift 2	8
10	4	0	DEF	shift 3	12	DEF	shift 3	12	DEF	shift 3	12	DEF	shift 3	12
11	0	4	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8
12	0	4	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8
13	0	4	ABC	shift 3	8	ABC	shift 3	8	ABC	shift 3	8	ABC	shift 3	8
14	4	0	DEF	shift 1	12	DEF	shift 1	12	DEF	shift 1	12	DEF	shift 1	12
15	4	0	ABC	shift 2	12	ABC	shift 2	12	ABC	shift 2	12	ABC	shift 2	12
16	0	4	DEF	shift 3	8	DEF	shift 3	8	DEF	shift 3	8	DEF	shift 3	8
17	0	0	EFG	OFF	0	EFG	OFF	0	EFG	OFF	0	EFG	OFF	0
Count hours

Cell Formulas
Range	Formula
B5:B17	B5	=COUNT(FILTER(F5:O5,(D$3:M$3>=B$3)*(D$3:M$3<=C$3)*(F$4:O$4="HOURS")*(F5:O5=12),""))
C5:C17	C5	=COUNT(FILTER(F5:O5,(D$3:M$3>=B$3)*(D$3:M$3<=C$3)*(F$4:O$4="HOURS")*(F5:O5=8),""))

 

[ExcelNewbie2020]

i figure it out using offset function.. thanks

posting my solution using countifs with offset function.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		EXPECTED RESULT
2		FROM	TO
3	Name	11-08-23	14-08-23	11-08-23			12-08-23			13-08-23			14-08-23
4		Count of 12 hrs	Count of 8hrs	Department	Shift	HOURS	Department	Shift	HOURS	Department	Shift	HOURS	Department	Shift	HOURS
5	name1	0	4	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8
6	name2	0	4	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8
7	name3	4	0	ABC	shift 3	12	ABC	shift 3	12	ABC	shift 3	12	ABC	shift 3	12
8	name4	0	0	DEF	shift 1	0	DEF	shift 1	0	DEF	shift 1	0	DEF	shift 1	0
9	name5	0	4	ABC	shift 2	8	ABC	shift 2	8	ABC	shift 2	8	ABC	shift 2	8
10	name6	4	0	DEF	shift 3	12	DEF	shift 3	12	DEF	shift 3	12	DEF	shift 3	12
11	name7	0	4	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8
12	name8	0	4	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8
13	name9	0	4	ABC	shift 3	8	ABC	shift 3	8	ABC	shift 3	8	ABC	shift 3	8
14	name10	4	0	DEF	shift 1	12	DEF	shift 1	12	DEF	shift 1	12	DEF	shift 1	12
15	name11	4	0	ABC	shift 2	12	ABC	shift 2	12	ABC	shift 2	12	ABC	shift 2	12
16	name12	0	4	DEF	shift 3	8	DEF	shift 3	8	DEF	shift 3	8	DEF	shift 3	8
17	name13	1	2	EFG	OFF	12	EFG	OFF	8	EFG	OFF	8	EFG	OFF	0
Sheet1

Cell Formulas
Range	Formula
B5:B17	B5	=COUNTIFS($D5:$O5,12,OFFSET($D$3:$O$3,0,-2),">="&$B$3,OFFSET($D$3:$O$3,0,-2),"<="&$C$3)
C5:C17	C5	=COUNTIFS($D5:$O5,8,OFFSET($D$3:$O$3,0,-2),">="&$B$3,OFFSET($D$3:$O$3,0,-2),"<="&$C$3)

 

[Peter_SSs]

posting my solution using countifs with offset function.

I don't know why you would use the volatile OFFSET function when it is not needed.
Did you try the non-volatile alternative?

It seems from your solution that Department and Shift cannot contain numbers so the non-volatile alternative can also be reduced as follows. It is both shorter and more efficient than using OFFSET

23 08 14.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	EXPECTED RESULT
2	FROM	TO
3	11/08/23	14/08/23	11/08/23			12/08/23			13/08/23			14/08/23
4	Count of 12 hrs	Count of 8hrs	Department	Shift	HOURS	Department	Shift	HOURS	Department	Shift	HOURS	Department	Shift	HOURS
5	0	4	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8
6	0	4	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8
7	4	0	ABC	shift 3	12	ABC	shift 3	12	ABC	shift 3	12	ABC	shift 3	12
8	0	0	DEF	shift 1	0	DEF	shift 1	0	DEF	shift 1	0	DEF	shift 1	0
9	0	4	ABC	shift 2	8	ABC	shift 2	8	ABC	shift 2	8	ABC	shift 2	8
10	4	0	DEF	shift 3	12	DEF	shift 3	12	DEF	shift 3	12	DEF	shift 3	12
11	0	4	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8
12	0	4	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8
13	0	4	ABC	shift 3	8	ABC	shift 3	8	ABC	shift 3	8	ABC	shift 3	8
14	4	0	DEF	shift 1	12	DEF	shift 1	12	DEF	shift 1	12	DEF	shift 1	12
15	4	0	ABC	shift 2	12	ABC	shift 2	12	ABC	shift 2	12	ABC	shift 2	12
16	0	4	DEF	shift 3	8	DEF	shift 3	8	DEF	shift 3	8	DEF	shift 3	8
17	1	2	EFG	OFF	12	EFG	OFF	8	EFG	OFF	8	EFG	OFF	0
Count hours

Cell Formulas
Range	Formula
B5:B17	B5	=COUNT(FILTER(F5:O5,(D$3:M$3>=B$3)*(D$3:M$3<=C$3)*(F5:O5=12),""))
C5:C17	C5	=COUNT(FILTER(F5:O5,(D$3:M$3>=B$3)*(D$3:M$3<=C$3)*(F5:O5=8),""))

 

[ExcelNewbie2020]

I don't know why you would use the volatile OFFSET function when it is not needed.
Did you try the non-volatile alternative?

It seems from your solution that Department and Shift cannot contain numbers so the non-volatile alternative can also be reduced as follows. It is both shorter and more efficient than using OFFSET

23 08 14.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	EXPECTED RESULT
2	FROM	TO
3	11/08/23	14/08/23	11/08/23			12/08/23			13/08/23			14/08/23
4	Count of 12 hrs	Count of 8hrs	Department	Shift	HOURS	Department	Shift	HOURS	Department	Shift	HOURS	Department	Shift	HOURS
5	0	4	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8
6	0	4	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8
7	4	0	ABC	shift 3	12	ABC	shift 3	12	ABC	shift 3	12	ABC	shift 3	12
8	0	0	DEF	shift 1	0	DEF	shift 1	0	DEF	shift 1	0	DEF	shift 1	0
9	0	4	ABC	shift 2	8	ABC	shift 2	8	ABC	shift 2	8	ABC	shift 2	8
10	4	0	DEF	shift 3	12	DEF	shift 3	12	DEF	shift 3	12	DEF	shift 3	12
11	0	4	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8
12	0	4	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8
13	0	4	ABC	shift 3	8	ABC	shift 3	8	ABC	shift 3	8	ABC	shift 3	8
14	4	0	DEF	shift 1	12	DEF	shift 1	12	DEF	shift 1	12	DEF	shift 1	12
15	4	0	ABC	shift 2	12	ABC	shift 2	12	ABC	shift 2	12	ABC	shift 2	12
16	0	4	DEF	shift 3	8	DEF	shift 3	8	DEF	shift 3	8	DEF	shift 3	8
17	1	2	EFG	OFF	12	EFG	OFF	8	EFG	OFF	8	EFG	OFF	0
Count hours

Cell Formulas
Range	Formula
B5:B17	B5	=COUNT(FILTER(F5:O5,(D$3:M$3>=B$3)*(D$3:M$3<=C$3)*(F5:O5=12),""))
C5:C17	C5	=COUNT(FILTER(F5:O5,(D$3:M$3>=B$3)*(D$3:M$3<=C$3)*(F5:O5=8),""))

Sir, I am aware that offset function its volatile. Actually, your formula was considered. However, when i transfer your formula to the user, i found out that his excel version is lower than 365.

 

[Peter_SSs]

However, when i transfer your formula to the user, i found out that his excel version is lower than 365.

Good idea to put that information in your first post if possible as helpers naturally otherwise assume the version(s) shown in your profile.

For lower versions at least as far back as 2010 you could try this non-volatile version.

23 08 20.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	FROM	TO
3	11/08/23	14/08/23	11/08/23			12/08/23			13/08/23			14/08/23
4	Count of 12 hrs	Count of 8hrs	Department	Shift	HOURS	Department	Shift	HOURS	Department	Shift	HOURS	Department	Shift	HOURS
5	0	4	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8
6	0	4	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8
7	4	0	ABC	shift 3	12	ABC	shift 3	12	ABC	shift 3	12	ABC	shift 3	12
8	0	0	DEF	shift 1	0	DEF	shift 1	0	DEF	shift 1	0	DEF	shift 1	0
9	0	4	ABC	shift 2	8	ABC	shift 2	8	ABC	shift 2	8	ABC	shift 2	8
10	4	0	DEF	shift 3	12	DEF	shift 3	12	DEF	shift 3	12	DEF	shift 3	12
11	0	4	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8	ABC	shift 1	8
12	0	4	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8	DEF	shift 2	8
13	0	4	ABC	shift 3	8	ABC	shift 3	8	ABC	shift 3	8	ABC	shift 3	8
14	4	0	DEF	shift 1	12	DEF	shift 1	12	DEF	shift 1	12	DEF	shift 1	12
15	4	0	ABC	shift 2	12	ABC	shift 2	12	ABC	shift 2	12	ABC	shift 2	12
16	0	4	DEF	shift 3	8	DEF	shift 3	8	DEF	shift 3	8	DEF	shift 3	8
17	1	2	EFG	OFF	12	EFG	OFF	8	EFG	OFF	8	EFG	OFF	0
Sheet2 (3)

Cell Formulas
Range	Formula
B5:B17	B5	=COUNTIFS(F5:O5,12,D$3:M$3,">="&B$3,D$3:M$3,"<="&C$3)
C5:C17	C5	=COUNTIFS(F5:O5,8,D$3:M$3,">="&B$3,D$3:M$3,"<="&C$3)