Needing Results in Indexed table to show only unhighlited values

[NVRensburg]

Hi there

I have a table that I need help populating some information. The formula is working perfectly in Columns F2:R25, but I just need to go a step further

In the fomula's in F2:R25, I want it to ONLY return the value's that are NOT highlighted in the table SITES (Column A & B)

Then in Column's F to R, I want it to Count the people that it populates in the above columns. At the moment it's counting all 24 entries.

Thanks in advance

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Current Site	Name			13	48A SEACLIFF	BENEFITZ	BRAND PARTNERS	CONSTELLATION	ELM PARK	HAUPAI SCHOOL	HILLSIDE	HOLY TRINITY SCHOOL	HUGHES & TUKE	JAZ'S HOUSE	JERVOIS	M ONE	MEADOWBANK PRIMARY
2	T BUILD	Aaron Anthony Pengelly				Aleksandar Savic	Rolando Colina	Iurii Makarov	Ariel Narvarte	Jesse Bailey	Goran Arsovski	Adrian Baclayo	Arnold Bonachita	Andrew Aceron	Selimir Vasovic	Alexander Salamat	Darko Radivojevic	Darko Miletic
3	HILLSIDE	Adrian Baclayo				Daniele Mella		Miodrag Puvalic	Erano Obal	Orlly Oyao	Ivan Zdravkovic	Alan Nazareno	Darwin Marquez	Baldo Manguna	Tamas Kozma	Arnold Llamera	Edmon Caisip	Srdjan Cebic
4	HILLSIDE	Alan Nazareno						Sead Asani	Juanito Crisostomo		Vladimir Vasovic	Brandon Lesoa				Elone Fotofili	Gilbert Manalili
5	NUMAT	Aldrin Dacanay							Lorenzo Mella			Celso Oceana				Enrique Dimdim	John Roy Corringham
6	48A SEACLIFF	Aleksandar Savic							Miku Torralba			Chris San Juan				Jerico Dela Cruz	Karl Ziento
7	SPENCER	Alex Devazas										Edgardo Cabamongan				Jerry Calicdan	Marnold Solejon
8	JERVOIS	Alexander Salamat										Emiliano Acoba					Ruan Moller
9	HUGHES & TUKE	Andrew Aceron										Joebert Dizon					Sean Brosnan
10	CONSTELLATION	Ariel Narvarte										Kristoffer Dela Cruz
11	HOLY TRINITY SCHOOL	Arnold Bonachita										Macario Cortezano
12	JERVOIS	Arnold Llamera										Massimiliano Pratino
13	HUGHES & TUKE	Baldo Manguna										Paul Vailua
14	HILLSIDE	Brandon Lesoa										Rey Pajo
15	PT ENGLAND PRIMARY	Branislav Gagic										Samiu Toki
16	SPENCER	Carlos Fajelga										Shannan Wilson
17	HILLSIDE	Celso Oceana										Taifeni Latu
18	HILLSIDE	Chris San Juan
19	SPENCER	Constant Ladreza
20		Danail Kazakov
21	48A SEACLIFF	Daniele Mella
22	PRINCES ST	Danilo Dimacali
23	T BUILD	Danilo Guevarra
24	MEADOWBANK PRIMARY	Darko Miletic
25	M ONE	Darko Radivojevic
26	HOLY TRINITY SCHOOL	Darwin Marquez
27	RYMAN	Dean Reynish			312	24	24	24	24	24	24	24	24	24	24	24	24	24
28	SPENCER	Eddie Ismael
29	HILLSIDE	Edgardo Cabamongan
30	M ONE	Edmon Caisip
31	JERVOIS	Elone Fotofili
32	HILLSIDE	Emiliano Acoba
33	JERVOIS	Enrique Dimdim
34	CONSTELLATION	Erano Obal
35	PRINCES ST	Eric Celestial
36	QUEEN ST	Fidel Mabanta
37	M ONE	Gilbert Manalili
38	HAUPAI SCHOOL	Goran Arsovski
39	PRINCES ST	Gwendolyn Nixon-Tamatea
40	BRAND PARTNERS	Iurii Makarov
41		Ivan Vulovic
42	HAUPAI SCHOOL	Ivan Zdravkovic
43	JERVOIS	Jerico Dela Cruz
44	JERVOIS	Jerry Calicdan
45	ELM PARK	Jesse Bailey
46	HILLSIDE	Joebert Dizon
47	M ONE	John Roy Corringham
48	SPENCER	Jose Monleon
49	SPENCER	Joseph Reyes
50	CONSTELLATION	Juanito Crisostomo
51	M ONE	Karl Ziento
52	HILLSIDE	Kristoffer Dela Cruz
53	CONSTELLATION	Lorenzo Mella
54	WAINUI SCHOOL	Louie Carlos
55	HILLSIDE	Macario Cortezano
56		Manraj Saroya
57	WAINUI SCHOOL	Marco Stojiljkovic
58	M ONE	Marnold Solejon
59	HILLSIDE	Massimiliano Pratino
60	SPENCER	Michael Marquez
61	CONSTELLATION	Miku Torralba
62	QUEEN ST	Milos Premovic
63	BRAND PARTNERS	Miodrag Puvalic
64	ELM PARK	Orlly Oyao
65	HILLSIDE	Paul Vailua
66	ROLEY BOLTON	Peter Ford
67	SPENCER	Ramil Reyes
68	STARBUCKS	Ramon Mariano
69	PARNELL	Rene Otsatalu
70	HILLSIDE	Rey Pajo
71	WAINUI SCHOOL	Rodel Villarina
72	BENEFITZ	Rolando Colina
73	SPENCER	Ronilo Lavesarez
74	M ONE	Ruan Moller
75	HILLSIDE	Samiu Toki
76	PARNELL	Samuel Phillips
77	BRAND PARTNERS	Sead Asani
78	M ONE	Sean Brosnan
79	JAZ'S HOUSE	Selimir Vasovic
80	HILLSIDE	Shannan Wilson
81	WAINUI SCHOOL	Slavko Jovanovic
82	MEADOWBANK PRIMARY	Srdjan Cebic
83	HILLSIDE	Taifeni Latu
84	JAZ'S HOUSE	Tamas Kozma
85	QUEEN ST	Vladimir Djordjevic
86	HAUPAI SCHOOL	Vladimir Vasovic
87	PARNELL	Vladimir Zikic
88	PRINCES ST	Wendy Tamatea
89	STARBUCKS	Zeljko Curcic
90
91
Sheet1

Cell Formulas
Range	Formula
E1	E1	=COUNTIF(F1:FO1,"*")
F2:F25,H2:H25,J2:J25,L2:L25,N2:N25,P2:P25,R2:R25	F2	=IFERROR(INDEX(SITES,SMALL(IF(SITES[Current Site]=F$1,ROW(SITES)-1),ROW(1:1)),2),"")
G2:G25,I2:I25,K2:K25,M2:M25,O2:O25,Q2:Q26	G2	=IFERROR(INDEX(SITES,SMALL(IF(SITES[Current Site]=G$1,ROW(SITES)-1),ROW(1:1)),2),"")
E27	E27	=SUM(F27:AH27)
F27:R27	F27	=COUNTIF(F2:F25,"*")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Fluff]

In F27 you can use

=COUNTIF(F2:F25,"?*")

However formulae cannot see cell format, although there is a work round, but the workbook will need to be saved as macro enabled.

 

[NVRensburg]

In F27 you can use

=COUNTIF(F2:F25,"?*")

However formulae cannot see cell format, although there is a work round, but the workbook will need to be saved as macro enabled.

Thanks so much, that works. If I save the workbook as Macro enabled, do you have a work around for me for the unhighligted values?

 

[Fluff]

Do you need it to work in 2016, or just 365?

 

[NVRensburg]

I have office 365, so that's perfect, thank you

 

[Fluff]

Ok, in the Name manager create a new name (I've called it IsColoured) & in the refers to box put

=GET.CELL(63,INDIRECT("rc[-2]",0))

Then in C2 put

=IsColoured

and fill down

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Current Site	Name	Colour		13	48A SEACLIFF	BENEFITZ	BRAND PARTNERS	CONSTELLATION	ELM PARK	HAUPAI SCHOOL	HILLSIDE	HOLY TRINITY SCHOOL	HUGHES & TUKE	JAZ'S HOUSE	JERVOIS	M ONE	MEADOWBANK PRIMARY
2	T BUILD	Aaron Anthony Pengelly	0			Daniele Mella	Rolando Colina	Iurii Makarov	Juanito Crisostomo	Jesse Bailey	Ivan Zdravkovic	Celso Oceana	Arnold Bonachita	Andrew Aceron	Selimir Vasovic	Alexander Salamat	Marnold Solejon	Srdjan Cebic
3	HILLSIDE	Adrian Baclayo	6					Miodrag Puvalic	Lorenzo Mella	Orlly Oyao		Edgardo Cabamongan	Darwin Marquez	Baldo Manguna		Elone Fotofili
4	HILLSIDE	Alan Nazareno	6						Miku Torralba			Emiliano Acoba				Jerico Dela Cruz
5	NUMAT	Aldrin Dacanay	6									Kristoffer Dela Cruz				Jerry Calicdan
6	48A SEACLIFF	Aleksandar Savic	6									Massimiliano Pratino
7	SPENCER	Alex Devazas	6									Paul Vailua
8	JERVOIS	Alexander Salamat	0
9	HUGHES & TUKE	Andrew Aceron	0
10	CONSTELLATION	Ariel Narvarte	6
11	HOLY TRINITY SCHOOL	Arnold Bonachita	0
12	JERVOIS	Arnold Llamera	6
13	HUGHES & TUKE	Baldo Manguna	0
14	HILLSIDE	Brandon Lesoa	6
15	PT ENGLAND PRIMARY	Branislav Gagic	6
16	SPENCER	Carlos Fajelga	6
17	HILLSIDE	Celso Oceana	0
18	HILLSIDE	Chris San Juan	6
19	SPENCER	Constant Ladreza	6
20		Danail Kazakov	43
21	48A SEACLIFF	Daniele Mella	0
22	PRINCES ST	Danilo Dimacali	0
23	T BUILD	Danilo Guevarra	0
24	MEADOWBANK PRIMARY	Darko Miletic	6
25	M ONE	Darko Radivojevic	6
26	HOLY TRINITY SCHOOL	Darwin Marquez	0
27	RYMAN	Dean Reynish	0		27	1	1	2	3	2	1	6	2	2	1	4	1	1
28	SPENCER	Eddie Ismael	6
29	HILLSIDE	Edgardo Cabamongan	0
30	M ONE	Edmon Caisip	6
31	JERVOIS	Elone Fotofili	0
32	HILLSIDE	Emiliano Acoba	0
33	JERVOIS	Enrique Dimdim	6
34	CONSTELLATION	Erano Obal	6
35	PRINCES ST	Eric Celestial	6
36	QUEEN ST	Fidel Mabanta	0
37	M ONE	Gilbert Manalili	6
38	HAUPAI SCHOOL	Goran Arsovski	6
39	PRINCES ST	Gwendolyn Nixon-Tamatea	0
40	BRAND PARTNERS	Iurii Makarov	0
41		Ivan Vulovic	0
42	HAUPAI SCHOOL	Ivan Zdravkovic	0
43	JERVOIS	Jerico Dela Cruz	0
44	JERVOIS	Jerry Calicdan	0
45	ELM PARK	Jesse Bailey	0
46	HILLSIDE	Joebert Dizon	6
47	M ONE	John Roy Corringham	6
48	SPENCER	Jose Monleon	6
49	SPENCER	Joseph Reyes	0
50	CONSTELLATION	Juanito Crisostomo	0
51	M ONE	Karl Ziento	6
52	HILLSIDE	Kristoffer Dela Cruz	0
53	CONSTELLATION	Lorenzo Mella	0
54	WAINUI SCHOOL	Louie Carlos	6
55	HILLSIDE	Macario Cortezano	6
56		Manraj Saroya	43
57	WAINUI SCHOOL	Marco Stojiljkovic	6
58	M ONE	Marnold Solejon	0
59	HILLSIDE	Massimiliano Pratino	0
60	SPENCER	Michael Marquez	6
61	CONSTELLATION	Miku Torralba	0
62	QUEEN ST	Milos Premovic	0
63	BRAND PARTNERS	Miodrag Puvalic	0
64	ELM PARK	Orlly Oyao	0
65	HILLSIDE	Paul Vailua	0
66	ROLEY BOLTON	Peter Ford	0
67	SPENCER	Ramil Reyes	6
68	STARBUCKS	Ramon Mariano	6
69	PARNELL	Rene Otsatalu	0
70	HILLSIDE	Rey Pajo	6
71	WAINUI SCHOOL	Rodel Villarina	0
72	BENEFITZ	Rolando Colina	0
73	SPENCER	Ronilo Lavesarez	6
74	M ONE	Ruan Moller	6
75	HILLSIDE	Samiu Toki	6
76	PARNELL	Samuel Phillips	0
77	BRAND PARTNERS	Sead Asani	6
78	M ONE	Sean Brosnan	6
79	JAZ'S HOUSE	Selimir Vasovic	0
80	HILLSIDE	Shannan Wilson	6
81	WAINUI SCHOOL	Slavko Jovanovic	6
82	MEADOWBANK PRIMARY	Srdjan Cebic	0
83	HILLSIDE	Taifeni Latu	6
84	JAZ'S HOUSE	Tamas Kozma	6
85	QUEEN ST	Vladimir Djordjevic	0
86	HAUPAI SCHOOL	Vladimir Vasovic	6
87	PARNELL	Vladimir Zikic	0
88	PRINCES ST	Wendy Tamatea	0
89	STARBUCKS	Zeljko Curcic	6
Main

Cell Formulas
Range	Formula
E1	E1	=COUNTIF(F1:FO1,"*")
F2:G2,K2,O2,Q2:R2,P2:P5,M2:N3,L2:L7,J2:J3,I2:I4,H2:H3	F2	=FILTER(Sites[[Name]:[Name]],(Sites[[Current Site]:[Current Site]]=F1)*(Sites[[Colour]:[Colour]]=0))
E27	E27	=SUM(F27:AH27)
F27:R27	F27	=COUNTIF(F2:F25,"?*")
C2:C89	C2	=IsColoured

 

[NVRensburg]

Thank you! I seem to be doing something wrong on my sheet, as "Constellation" isn't showing any values, and neither are some other sites? Any idea what I'm doing wrong?

Fit Test Sites Update Nov 2020.xlsx
	A	B	C	D										N	O	P	Q	R	S	T	U	V
1	Current Site	Name	Colour												25	48A SEACLIFF	BENEFITZ	BRAND PARTNERS	CONSTELLATION	ELM PARK	HAUPAI SCHOOL	HILLSIDE
2	T BUILD	Aaron Anthony Pengelly	0													Daniele Mella	Rolando Colina	Iurii Makarov	#SPILL!	Jesse Bailey	Ivan Zdravkovic	Celso Oceana
3	HILLSIDE	Adrian Baclayo	6													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
4	HILLSIDE	Alan Nazareno	6													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
5	NUMAT	Aldrin Dacanay	6													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
6	48A SEACLIFF	Aleksandar Savic	6													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
7	SPENCER	Alex Devazas	6													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
8	JERVOIS	Alexander Salamat	0													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
9	HUGHES & TUKE	Andrew Aceron	0													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
10	CONSTELLATION	Ariel Narvarte	6													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
11	HOLY TRINITY SCHOOL	Arnold Bonachita	0													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
12	JERVOIS	Arnold Llamera	6													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
13	HUGHES & TUKE	Baldo Manguna	0													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
14	HILLSIDE	Brandon Lesoa	6													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
15	PT ENGLAND PRIMARY	Branislav Gagic	6													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
16	SPENCER	Carlos Fajelga	6													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
17	HILLSIDE	Celso Oceana	0													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
18	HILLSIDE	Chris San Juan	6													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
19	SPENCER	Constant Ladreza	6													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
20		Danail Kazakov	0													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
21	48A SEACLIFF	Daniele Mella	0													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
22	PRINCES ST	Danilo Dimacali	0													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
23	T BUILD	Danilo Guevarra	0													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
24	MEADOWBANK PRIMARY	Darko Miletic	6													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
25	M ONE	Darko Radivojevic	6													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
26	HOLY TRINITY SCHOOL	Darwin Marquez	0													#CALC!	#CALC!	#CALC!	#SPILL!	#CALC!	#CALC!	#CALC!
27	RYMAN	Dean Reynish	0												19	1	1	1	0	1	1	1
28	SPENCER	Eddie Ismael	6
29	HILLSIDE	Edgardo Cabamongan	0
30	M ONE	Edmon Caisip	6
31	JERVOIS	Elone Fotofili	0
32	HILLSIDE	Emiliano Acoba	0
33	JERVOIS	Enrique Dimdim	6
34	CONSTELLATION	Erano Obal	6
35	PRINCES ST	Eric Celestial	6
36	QUEEN ST	Fidel Mabanta	0
37	M ONE	Gilbert Manalili	6
38	HAUPAI SCHOOL	Goran Arsovski	6
39	PRINCES ST	Gwendolyn Nixon-Tamatea	0
40	BRAND PARTNERS	Iurii Makarov	0
41		Ivan Vulovic	6
42	HAUPAI SCHOOL	Ivan Zdravkovic	0
43	JERVOIS	Jerico Dela Cruz	0
44	JERVOIS	Jerry Calicdan	0
45	ELM PARK	Jesse Bailey	0
46	HILLSIDE	Joebert Dizon	6
47	M ONE	John Roy Corringham	6
48	SPENCER	Jose Monleon	6
49	SPENCER	Joseph Reyes	0
50	CONSTELLATION	Juanito Crisostomo	0
51	M ONE	Karl Ziento	6
52	HILLSIDE	Kristoffer Dela Cruz	0
53	CONSTELLATION	Lorenzo Mella	0
54	WAINUI SCHOOL	Louie Carlos	6
55	HILLSIDE	Macario Cortezano	6
56		Manraj Saroya	0
57	WAINUI SCHOOL	Marco Stojiljkovic	6
58	M ONE	Marnold Solejon	0
59	HILLSIDE	Massimiliano Pratino	0
60	SPENCER	Michael Marquez	6
61	CONSTELLATION	Miku Torralba	0
62	QUEEN ST	Milos Premovic	0
63	BRAND PARTNERS	Miodrag Puvalic	6
64	ELM PARK	Orlly Oyao	0
65	HILLSIDE	Paul Vailua	0
66	ROLEY BOLTON	Peter Ford	0
67	SPENCER	Ramil Reyes	6
68	STARBUCKS	Ramon Mariano	6
69	PARNELL	Rene Otsatalu	0
70	HILLSIDE	Rey Pajo	6
71	WAINUI SCHOOL	Rodel Villarina	0
72	BENEFITZ	Rolando Colina	0
73	SPENCER	Ronilo Lavesarez	6
74	M ONE	Ruan Moller	6
75	HILLSIDE	Samiu Toki	6
76	PARNELL	Samuel Phillips	0
77	BRAND PARTNERS	Sead Asani	6
78	M ONE	Sean Brosnan	6
79	JAZ'S HOUSE	Selimir Vasovic	0
80	HILLSIDE	Shannan Wilson	6
81	WAINUI SCHOOL	Slavko Jovanovic	6
82	MEADOWBANK PRIMARY	Srdjan Cebic	0
83	HILLSIDE	Taifeni Latu	6
84	JAZ'S HOUSE	Tamas Kozma	6
85	QUEEN ST	Vladimir Djordjevic	0
86	HAUPAI SCHOOL	Vladimir Vasovic	6
87	PARNELL	Vladimir Zikic	0
88	PRINCES ST	Wendy Tamatea	0
89	STARBUCKS	Zeljko Curcic	6
INSERT SITES HERE

Cell Formulas
Range	Formula
O1	O1	=COUNTIF(P1:GU1,"*")
P2,S2:S19	P2	=FILTER(SITES[[Name]:[Name]],(SITES[[Current Site]:[Current Site]]=P1)*(SITES[[Colour]:[Colour]]=0))
Q2:R2,T2:V19,P3:R26,S20:V26	Q2	=FILTER(SITES[[Name]:[Name]],(SITES[[Current Site]:[Current Site]]=Q1)*(SITES[[Colour]:[Colour]]=0))
O27	O27	=SUM(P27:BN27)
P27:V27	P27	=COUNTIF(P2:P25,"?*")
C2:C89	C2	=IsColoured
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P2:AN26	Cell	contains an error	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A1:A89	List	=$F:$F

 

[Fluff]

The formula should only go in the 1st row & should not not be copied down. Also it should not be entered with Ctrl Shift Enter, but just with Enter.

 

[NVRensburg]

Sorry I don't understand? How does the list populate if I don't copy it down? I've dragged it across but still nothing in the columns that should have values?

Fit Test Sites Update Nov 2020.xlsx
	A	B	C	D										N	O	P	Q	R	S	T	U	V
1	Current Site	Name	Colour												25	48A SEACLIFF	BENEFITZ	BRAND PARTNERS	CONSTELLATION	ELM PARK	HAUPAI SCHOOL	HILLSIDE
2	T BUILD	Aaron Anthony Pengelly	0													Daniele Mella	Rolando Colina	Iurii Makarov	#SPILL!	#SPILL!	Ivan Zdravkovic	#SPILL!
3	HILLSIDE	Adrian Baclayo	6													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
4	HILLSIDE	Alan Nazareno	6													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
5	NUMAT	Aldrin Dacanay	6													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
6	48A SEACLIFF	Aleksandar Savic	6													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
7	SPENCER	Alex Devazas	6													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
8	JERVOIS	Alexander Salamat	0													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
9	HUGHES & TUKE	Andrew Aceron	0													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
10	CONSTELLATION	Ariel Narvarte	6													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
11	HOLY TRINITY SCHOOL	Arnold Bonachita	0													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
12	JERVOIS	Arnold Llamera	6													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
13	HUGHES & TUKE	Baldo Manguna	0													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
14	HILLSIDE	Brandon Lesoa	6													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
15	PT ENGLAND PRIMARY	Branislav Gagic	6													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
16	SPENCER	Carlos Fajelga	6													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
17	HILLSIDE	Celso Oceana	0													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
18	HILLSIDE	Chris San Juan	6													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
19	SPENCER	Constant Ladreza	6													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
20		Danail Kazakov	0													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
21	48A SEACLIFF	Daniele Mella	0													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
22	PRINCES ST	Danilo Dimacali	0													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
23	T BUILD	Danilo Guevarra	0													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
24	MEADOWBANK PRIMARY	Darko Miletic	6													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
25	M ONE	Darko Radivojevic	6													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
26	HOLY TRINITY SCHOOL	Darwin Marquez	0													#CALC!	#CALC!	#CALC!	#SPILL!	#SPILL!	#CALC!	#SPILL!
27	RYMAN	Dean Reynish	0												16	1	1	1	0	0	1	0
28	SPENCER	Eddie Ismael	6
29	HILLSIDE	Edgardo Cabamongan	0
30	M ONE	Edmon Caisip	6
31	JERVOIS	Elone Fotofili	0
32	HILLSIDE	Emiliano Acoba	0
33	JERVOIS	Enrique Dimdim	6
34	CONSTELLATION	Erano Obal	6
35	PRINCES ST	Eric Celestial	6
36	QUEEN ST	Fidel Mabanta	0
37	M ONE	Gilbert Manalili	6
38	HAUPAI SCHOOL	Goran Arsovski	6
39	PRINCES ST	Gwendolyn Nixon-Tamatea	0
40	BRAND PARTNERS	Iurii Makarov	0
41		Ivan Vulovic	6
42	HAUPAI SCHOOL	Ivan Zdravkovic	0
43	JERVOIS	Jerico Dela Cruz	0
44	JERVOIS	Jerry Calicdan	0
45	ELM PARK	Jesse Bailey	0
46	HILLSIDE	Joebert Dizon	6
47	M ONE	John Roy Corringham	6
48	SPENCER	Jose Monleon	6
49	SPENCER	Joseph Reyes	0
50	CONSTELLATION	Juanito Crisostomo	0
51	M ONE	Karl Ziento	6
52	HILLSIDE	Kristoffer Dela Cruz	0
53	CONSTELLATION	Lorenzo Mella	0
54	WAINUI SCHOOL	Louie Carlos	6
55	HILLSIDE	Macario Cortezano	6
56		Manraj Saroya	0
57	WAINUI SCHOOL	Marco Stojiljkovic	6
58	M ONE	Marnold Solejon	0
59	HILLSIDE	Massimiliano Pratino	0
60	SPENCER	Michael Marquez	6
61	CONSTELLATION	Miku Torralba	0
62	QUEEN ST	Milos Premovic	0
63	BRAND PARTNERS	Miodrag Puvalic	6
64	ELM PARK	Orlly Oyao	0
65	HILLSIDE	Paul Vailua	0
66	ROLEY BOLTON	Peter Ford	0
67	SPENCER	Ramil Reyes	6
68	STARBUCKS	Ramon Mariano	6
69	PARNELL	Rene Otsatalu	0
70	HILLSIDE	Rey Pajo	6
71	WAINUI SCHOOL	Rodel Villarina	0
72	BENEFITZ	Rolando Colina	0
73	SPENCER	Ronilo Lavesarez	6
74	M ONE	Ruan Moller	6
75	HILLSIDE	Samiu Toki	6
76	PARNELL	Samuel Phillips	0
77	BRAND PARTNERS	Sead Asani	6
78	M ONE	Sean Brosnan	6
79	JAZ'S HOUSE	Selimir Vasovic	0
80	HILLSIDE	Shannan Wilson	6
81	WAINUI SCHOOL	Slavko Jovanovic	6
82	MEADOWBANK PRIMARY	Srdjan Cebic	0
83	HILLSIDE	Taifeni Latu	6
84	JAZ'S HOUSE	Tamas Kozma	6
85	QUEEN ST	Vladimir Djordjevic	0
86	HAUPAI SCHOOL	Vladimir Vasovic	6
87	PARNELL	Vladimir Zikic	0
88	PRINCES ST	Wendy Tamatea	0
89	STARBUCKS	Zeljko Curcic	6
INSERT SITES HERE

Cell Formulas
Range	Formula
O1	O1	=COUNTIF(P1:GU1,"*")
P2:V2,S3:S19	P2	=FILTER(SITES[[Name]:[Name]],(SITES[[Current Site]:[Current Site]]=P1)*(SITES[[Colour]:[Colour]]=0))
T3:V19,P3:R26,S20:V26	P3	=FILTER(SITES[[Name]:[Name]],(SITES[[Current Site]:[Current Site]]=P2)*(SITES[[Colour]:[Colour]]=0))
O27	O27	=SUM(P27:BN27)
P27:V27	P27	=COUNTIF(P2:P25,"?*")
C2:C89	C2	=IsColoured
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P2:AN26	Cell	contains an error	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A1:A89	List	=$F:$F

 

[Fluff]

Firstly I would recommend you remove the conditional formatting that hides errors, especially when testing new formulae.
Once you have done that you will see that S2 is not blank, it has an error.
Then I suggest you try what I have suggested regards how to enter the formula & remove the formula in the cells from row 3 downwards.