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.

 

[NVRensburg]

Thank you so much! I've got it to work finally.
I appreciate all your time and effort in helping me get this to work!!
Have a great day further

 

[Fluff]

You're welcome & thanks for the feedback.
I suspect that you have never used the new dynamic array formulae before.