Formula not working

[esmarques]

Hello, could anyone help to find out why the formulas to show the "No Data" numbers not working? These are the cells: AD11-AO11, AD26-AP26, AD37-AP37
It was working fine, but when I have added some columns it stopped. I tried everything but coudn't figure it out.

In the sheet I need to know the number of people that didn't entered any information in relation the specific table details.

Please let me know if more details is needed.

Thank you

Data analysis V1.xlsx
	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	AL	AM	AN	AO	AP	AQ
1	in	out	referrer	date	time	name	postcode	email	phone	reason	artist	staff	visitor	volunteer	participant	tutor	trades	delivery	walk	drive	bus	train	cycle	audience	atendee	District	Month
2		out	81.149.159.151	4/30/23	16:55:58	Mark	M4 4EE			Office			TRUE							TRUE						Manchester	Apr-23			Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	TOTAL	Average
3	in		81.149.159.151	4/30/23	13:05:08	Karen	m125lr			Office										TRUE						Manchester	Apr-23		artist	1	0	0	0	0	0	0	0	0	0	0	0	1
4	in		81.149.159.151	4/30/23	11:51:01	Mel	mossley			tutor	TRUE										TRUE					Not found	Apr-23		staff	1	1	0	0	0	0	0	0	0	0	0	0	2
5	in		81.149.159.151	4/30/23	9:44:39	Dan	ol5 0dw			Crafty		TRUE														Tameside	Apr-23		visitor	1	0	0	0	0	0	0	0	0	0	0	0	1
6	in		81.149.159.151	5/30/23	9:44:39	Dan	ol5 0dw			Crafty		TRUE														Tameside	May-23		volunteer	0	0	0	0	0	0	0	0	0	0	0	0	0
7																													participant	0	0	0	0	0	0	0	0	0	0	0	0	0
8																													tutor	0	0	0	0	0	0	0	0	0	0	0	0	0
9																													trades	0	0	0	0	0	0	0	0	0	0	0	0	0
10																													delivery	0	0	0	0	0	0	0	0	0	0	0	0	0
11																													No Data	0	0	0	0	0	0	0	0	0	0	0	0	0
12																													TOTAL	3	1	0	0	0	0	0	0	0	0	0	0	4
13
14
15																														Bolton	Bury	Manchester	Oldham	Rochdale	Salford	Stockport	Tameside	Trafford	Wigan	Not Found	GM Non-Disttit Specific	Users Outside GM	TOTAL
16																													artist	0	0	0	0	0	0	0	0	0	0	1	0	0	1
17																													staff	0	0	0	0	0	0	0	1	0	0	0	0	0	1
18																													visitor	0	0	1	0	0	0	0	0	0	0	0	0	0	1
19																													volunteer	0	0	0	0	0	0	0	0	0	0	0	0	0	0
20																													participant	0	0	0	0	0	0	0	0	0	0	0	0	0	0
21																													tutor	0	0	0	0	0	0	0	0	0	0	0	0	0	0
22																													trades	0	0	0	0	0	0	0	0	0	0	0	0	0	0
23																													delivery	0	0	0	0	0	0	0	0	0	0	0	0	0	0
24																													audience	0	0	0	0	0	0	0	0	0	0	0	0	0	0
25																													atendee	0	0	0	0	0	0	0	0	0	0	0	0	0	0
26																													No Data	0	0	0	0	0	0	0	0	0	0	0	0	0	0
27																													TOTAL	0	0	1	0	0	0	0	1	0	0	1	0	0	3
28
29
30
31																														Bolton	Bury	Manchester	Oldham	Rochdale	Salford	Stockport	Tameside	Trafford	Wigan	Not Found	GM Non-Disttit Specific	Users Outside GM	TOTAL
32																													walk	0	0	0	0	0	0	0	0	0	0	0	0	0	0
33																													drive	0	0	2	0	0	0	0	0	0	0	0	0	0	2
34																													bus	0	0	0	0	0	0	0	0	0	0	1	0	0	1
35																													train	0	0	0	0	0	0	0	0	0	0	0	0	0	0
36																													cycle	0	0	0	0	0	0	0	0	0	0	0	0	0	0
37																													No Data	0	0	0	0	0	0	0	0	0	0	0	0	0	0
38																													TOTAL	0	0	2	0	0	0	0	0	0	0	1	0	0	3
Sheet1

Cell Formulas
Range	Formula
AD3:AO3	AD3	=COUNTIFS($K:$K,"TRUE",$AA:$AA,AD$2)
AP3:AP11	AP3	=SUM(AD3:AO3)
AD4:AO4	AD4	=COUNTIFS($L:$L,"TRUE",$AA:$AA,AD$2)
AD5:AO5	AD5	=COUNTIFS($M:$M,"TRUE",$AA:$AA,AD$2)
AD6:AO6	AD6	=COUNTIFS($N:$N,"TRUE",$AA:$AA,AD$2)
AD7:AO7	AD7	=COUNTIFS($O:$O,"TRUE",$AA:$AA,AD$2)
AD8:AO8	AD8	=COUNTIFS($P:$P,"TRUE",$AA:$AA,AD$2)
AD9:AO9	AD9	=COUNTIFS($Q:$Q,"TRUE",$AA:$AA,AD$2)
AD10:AO10	AD10	=COUNTIFS($R:$R,"TRUE",$AA:$AA,AD$2)
AD11:AO11	AD11	=SUM(--(MMULT(($AA$2:$AA$4976=AD2)*($K$2:$Y$4976=""),SEQUENCE(15,,,0))=15))
AD12:AP12	AD12	=SUM(AD3:AD10)
AA2:AA6	AA2	=TEXT(D2,"mmm-YY")
AD16	AD16	=IFERROR(ROWS(UNIQUE(FILTER($F$2:$F$4975,($Z$2:$Z$4975=AD$15)*(INDEX($K$2:$Y$4975,,XMATCH($AC16,$K$1:$Y$1)))))),0)
AE16:AN25	AE16	=IFERROR(ROWS(UNIQUE(FILTER($F$2:$F$974,($Z$2:$Z$974=AE$15)*(INDEX($K$2:$Y$974,,XMATCH($AC16,$K$1:$Y$1)))))),0)
AO16	AO16	=COUNTIFS($K:$K,"TRUE",$Z:$Z,AO$15)
AP16:AP25	AP16	=IFERROR(ROWS(UNIQUE(FILTER($F$2:$F$974,(ISNA(XMATCH($Z$2:$Z$974,$AD$15:$AN$15)))*(INDEX($K$2:$Y$974,,XMATCH($AC16,$K$1:$Y$1)))))),0)
AQ16:AQ26,AQ32:AQ37	AQ16	=SUM(AD16:AP16)
AD17:AD25	AD17	=IFERROR(ROWS(UNIQUE(FILTER($F$2:$F$4975,($Z$2:$Z$4975=AD$15)*(INDEX($K$2:$Y$4975,,XMATCH($AC17,$K$1:$Y$1)))))),0)
AO17	AO17	=COUNTIFS($L:$L,"TRUE",$Z:$Z,AO$15)
AO18	AO18	=COUNTIFS($M:$M,"TRUE",$Z:$Z,AO$15)
AO19	AO19	=COUNTIFS($N:$N,"TRUE",$Z:$Z,AO$15)
AO20	AO20	=COUNTIFS($O:$O,"TRUE",$Z:$Z,AO$15)
AO21	AO21	=COUNTIFS($P:$P,"TRUE",$Z:$Z,AO$15)
AO22	AO22	=COUNTIFS($Q:$Q,"TRUE",$Z:$Z,AO$15)
AO23	AO23	=COUNTIFS($R:$R,"TRUE",$Z:$Z,AO$15)
AO24	AO24	=COUNTIFS($X:$X,"TRUE",$Z:$Z,AO$15)
AO25	AO25	=COUNTIFS($Y:$Y,"TRUE",$Z:$Z,AO$15)
AD26:AP26	AD26	=SUM(--(MMULT(($Z$2:$Z$4976=AD15)*($K$2:$Y$4976=""),SEQUENCE(15,,,0))=15))
AD27:AQ27	AD27	=SUM(AD16:AD23)
AD32:AN36	AD32	=IFERROR(ROWS(UNIQUE(FILTER($F$2:$F$4976,($Z$2:$Z$4976=AD$31)*(INDEX($K$2:$Y$4976,,XMATCH($AC32,$K$1:$Y$1)))))),0)
AO32:AP36	AO32	=IFERROR(ROWS(UNIQUE(FILTER($F$2:$F$974,($Z$2:$Z$974=AO$31)*(INDEX($K$2:$Y$974,,XMATCH($AC32,$K$1:$Y$1)))))),0)
AD37:AP37	AD37	=SUM(--(MMULT(($Z$2:$Z$974=AD31)*($K$2:$Y$974=""),SEQUENCE(15,,,0))=15))
AD38:AQ38	AD38	=SUM(AD32:AD38)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RoryA]

You appear to have circular references. For example the formula in An38 includes a reference to AN38.

 

[esmarques]

You appear to have circular references. For example the formula in An38 includes a reference to AN38.

Thank you very much