How to use countifs with multiple criteria and one criteria will look up in more than 2 columns to count

[shamanlady02]

I tried to extend the criteria range in other column if possible to lookup the match part (where in M will match any value within column M to AC) then will meet the other criteria includes in countifs but result became value. Any suggestions will do. TIA ?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1	RANGE	TICKET	FAULT DESCRIPTION	REPEATER COUNT	SAME DIS COUNT	COMPLAINT TYPE	PRIORITY	MONTH	TYPE	PART	CAUSE:ENTITY	CAUSE	ENTITY	CAUSE	ENTITY	CAUSE	ENTITY	CAUSE	ENTITY	CAUSE	ENTITY	CAUSE	ENTITY	CAUSE	ENTITY	CAUSE	ENTITY	CAUSE	ENTITY	TELEPHONE #	SERVICE ID	STATUS	AGREED DATE	SERVICE TYPE	PACKAGE	DATE EXTRACTED	DATE COMPLETED
2	NOT PQI	1		1	#VALUE!	VOICE					124:451, 174:457	124	451	174	457															1223		COMPLETED					9/1/2020
3	NOT PQI	2		4	1	DATA					143:453	143	453																	1224		COMPLETED					9/1/2020
4	NOT PQI	3		4	1	DATA					143:453, 274:456, 4G0:30H	143	453	274	456	4G0	30H													1224		COMPLETED					9/1/2020
5	NOT PQI	4		1	1	DATA					274:45S, 27M:30H, 27M:30W	274	45S	27M	30H	27M	30W													1225		COMPLETED					9/1/2020
6	NOT PQI	5		0	1	VOICE					124:451, 274:45S	124	451	274	45S															1223		COMPLETED					9/8/2020
7	NOT PQI	5		0	1	DATA					274:4L4, GRD:4FQ	274	4L4	GRD	4FQ															1224		COMPLETED					9/10/2020
8	NOT PQI	6		3	1	DATA					M96:M72	M96	M72																	1226		COMPLETED					9/1/2020
9	NOT PQI	7		1	1	DATA					574:443	574	443																	1227		COMPLETED					9/1/2020
10	NOT PQI	8		0	1	VOICE					124:451	124	451																	1229		COMPLETED					9/1/2020
Sheet1

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS($AD$2:$AD$976693,$AD2,$M$2:$AC$976693,$M2,$B$2:$B$976693,"<>",$AK$2:$AK$976693,"<="&$AK2+30)
E3:E10	E3	=COUNTIFS($AD$2:$AD$976693,$AD3,$M$2:$M$976693,$M3,$B$2:$B$976693,"<>",$AK$2:$AK$976693,"<="&$AK3+30)

 

[Fluff]

Hi & welcome to MrExcel.
How many rows of data do you actually have?

 

[shamanlady02]

thank you actually I have until 976693 Rows

 

[Fluff]

Ouch.
You can try this

=SUMPRODUCT(($AD$2:$AD$976693=$AD2)*($M$2:$AC$976693=$M2)*($B$2:$B$976693<>"")*($AK$2:$AK$976693<=$AK2+30))

but it could well crash with that amount of data.

 

[shamanlady02]

Ouch.
You can try this

=SUMPRODUCT(($AD$2:$AD$976693=$AD2)*($M$2:$AC$976693=$M2)*($B$2:$B$976693<>"")*($AK$2:$AK$976693<=$AK2+30))

but it could well crash with that amount of data.

it works! thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.