Needing Results in Indexed table to show only unhighlited values

NVRensburg

Board Regular
Joined
Jul 1, 2014
Messages
76
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQR
1Current SiteName1348A SEACLIFFBENEFITZBRAND PARTNERSCONSTELLATIONELM PARKHAUPAI SCHOOLHILLSIDE HOLY TRINITY SCHOOLHUGHES & TUKEJAZ'S HOUSEJERVOISM ONEMEADOWBANK PRIMARY
2T BUILDAaron Anthony PengellyAleksandar SavicRolando ColinaIurii MakarovAriel NarvarteJesse BaileyGoran ArsovskiAdrian BaclayoArnold BonachitaAndrew AceronSelimir VasovicAlexander SalamatDarko RadivojevicDarko Miletic
3HILLSIDE Adrian BaclayoDaniele Mella Miodrag PuvalicErano ObalOrlly OyaoIvan ZdravkovicAlan NazarenoDarwin MarquezBaldo MangunaTamas KozmaArnold LlameraEdmon CaisipSrdjan Cebic
4HILLSIDE Alan Nazareno  Sead AsaniJuanito Crisostomo Vladimir VasovicBrandon Lesoa   Elone FotofiliGilbert Manalili 
5NUMATAldrin Dacanay   Lorenzo Mella  Celso Oceana   Enrique DimdimJohn Roy Corringham 
648A SEACLIFFAleksandar Savic   Miku Torralba  Chris San Juan   Jerico Dela CruzKarl Ziento 
7SPENCERAlex Devazas      Edgardo Cabamongan   Jerry CalicdanMarnold Solejon 
8JERVOISAlexander Salamat      Emiliano Acoba    Ruan Moller 
9HUGHES & TUKEAndrew Aceron      Joebert Dizon    Sean Brosnan 
10CONSTELLATIONAriel Narvarte      Kristoffer Dela Cruz      
11HOLY TRINITY SCHOOLArnold Bonachita      Macario Cortezano      
12JERVOISArnold Llamera      Massimiliano Pratino      
13HUGHES & TUKEBaldo Manguna      Paul Vailua      
14HILLSIDE Brandon Lesoa      Rey Pajo      
15PT ENGLAND PRIMARYBranislav Gagic       Samiu Toki      
16SPENCERCarlos Fajelga      Shannan Wilson      
17HILLSIDE Celso Oceana      Taifeni Latu      
18HILLSIDE Chris San Juan             
19SPENCERConstant Ladreza             
20Danail Kazakov             
2148A SEACLIFFDaniele Mella             
22PRINCES STDanilo Dimacali             
23T BUILDDanilo Guevarra             
24MEADOWBANK PRIMARYDarko Miletic             
25M ONEDarko Radivojevic             
26HOLY TRINITY SCHOOLDarwin Marquez 
27RYMANDean Reynish31224242424242424242424242424
28SPENCEREddie Ismael
29HILLSIDE Edgardo Cabamongan
30M ONEEdmon Caisip
31JERVOISElone Fotofili
32HILLSIDE Emiliano Acoba
33JERVOISEnrique Dimdim
34CONSTELLATIONErano Obal
35PRINCES STEric Celestial
36QUEEN STFidel Mabanta
37M ONEGilbert Manalili
38HAUPAI SCHOOLGoran Arsovski
39PRINCES STGwendolyn Nixon-Tamatea
40BRAND PARTNERSIurii Makarov
41Ivan Vulovic
42HAUPAI SCHOOLIvan Zdravkovic
43JERVOISJerico Dela Cruz
44JERVOISJerry Calicdan
45ELM PARKJesse Bailey
46HILLSIDE Joebert Dizon
47M ONEJohn Roy Corringham
48SPENCERJose Monleon
49SPENCERJoseph Reyes
50CONSTELLATIONJuanito Crisostomo
51M ONEKarl Ziento
52HILLSIDE Kristoffer Dela Cruz
53CONSTELLATIONLorenzo Mella
54WAINUI SCHOOLLouie Carlos
55HILLSIDE Macario Cortezano
56Manraj Saroya
57WAINUI SCHOOLMarco Stojiljkovic
58M ONEMarnold Solejon
59HILLSIDE Massimiliano Pratino
60SPENCERMichael Marquez
61CONSTELLATIONMiku Torralba
62QUEEN STMilos Premovic
63BRAND PARTNERSMiodrag Puvalic
64ELM PARKOrlly Oyao
65HILLSIDE Paul Vailua
66ROLEY BOLTONPeter Ford
67SPENCERRamil Reyes
68STARBUCKSRamon Mariano
69PARNELLRene Otsatalu
70HILLSIDE Rey Pajo
71WAINUI SCHOOLRodel Villarina
72BENEFITZRolando Colina
73SPENCERRonilo Lavesarez
74M ONERuan Moller
75HILLSIDE Samiu Toki
76PARNELLSamuel Phillips
77BRAND PARTNERSSead Asani
78M ONESean Brosnan
79JAZ'S HOUSESelimir Vasovic
80HILLSIDE Shannan Wilson
81WAINUI SCHOOLSlavko Jovanovic
82MEADOWBANK PRIMARYSrdjan Cebic
83HILLSIDE Taifeni Latu
84JAZ'S HOUSETamas Kozma
85QUEEN STVladimir Djordjevic
86HAUPAI SCHOOLVladimir Vasovic
87PARNELLVladimir Zikic
88PRINCES STWendy Tamatea
89STARBUCKSZeljko Curcic
90
91
Sheet1
Cell Formulas
RangeFormula
E1E1=COUNTIF(F1:FO1,"*")
F2:F25,H2:H25,J2:J25,L2:L25,N2:N25,P2:P25,R2:R25F2=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:Q26G2=IFERROR(INDEX(SITES,SMALL(IF(SITES[Current Site]=G$1,ROW(SITES)-1),ROW(1:1)),2),"")
E27E27=SUM(F27:AH27)
F27:R27F27=COUNTIF(F2:F25,"*")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

NVRensburg

Board Regular
Joined
Jul 1, 2014
Messages
76
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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 :)
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
I suspect that you have never used the new dynamic array formulae before.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,351
Messages
5,601,132
Members
414,429
Latest member
Bilaal xaka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top