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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,266
Office Version
  1. 365
Platform
  1. Windows
In F27 you can use
Excel Formula:
=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

Board Regular
Joined
Jul 1, 2014
Messages
76
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
In F27 you can use
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,266
Office Version
  1. 365
Platform
  1. Windows
Do you need it to work in 2016, or just 365?
 

NVRensburg

Board Regular
Joined
Jul 1, 2014
Messages
76
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,266
Office Version
  1. 365
Platform
  1. Windows
Ok, in the Name manager create a new name (I've called it IsColoured) & in the refers to box put
Excel Formula:
=GET.CELL(63,INDIRECT("rc[-2]",0))
Then in C2 put
Excel Formula:
=IsColoured
and fill down

+Fluff v2.xlsm
ABCDEFGHIJKLMNOPQR
1Current SiteNameColour1348A SEACLIFFBENEFITZBRAND PARTNERSCONSTELLATIONELM PARKHAUPAI SCHOOLHILLSIDE HOLY TRINITY SCHOOLHUGHES & TUKEJAZ'S HOUSEJERVOISM ONEMEADOWBANK PRIMARY
2T BUILDAaron Anthony Pengelly0Daniele MellaRolando ColinaIurii MakarovJuanito CrisostomoJesse BaileyIvan ZdravkovicCelso OceanaArnold BonachitaAndrew AceronSelimir VasovicAlexander SalamatMarnold SolejonSrdjan Cebic
3HILLSIDE Adrian Baclayo6Miodrag PuvalicLorenzo MellaOrlly OyaoEdgardo CabamonganDarwin MarquezBaldo MangunaElone Fotofili
4HILLSIDE Alan Nazareno6Miku TorralbaEmiliano AcobaJerico Dela Cruz
5NUMATAldrin Dacanay6Kristoffer Dela CruzJerry Calicdan
648A SEACLIFFAleksandar Savic6Massimiliano Pratino
7SPENCERAlex Devazas6Paul Vailua
8JERVOISAlexander Salamat0
9HUGHES & TUKEAndrew Aceron0
10CONSTELLATIONAriel Narvarte6
11HOLY TRINITY SCHOOLArnold Bonachita0
12JERVOISArnold Llamera6
13HUGHES & TUKEBaldo Manguna0
14HILLSIDE Brandon Lesoa6
15PT ENGLAND PRIMARYBranislav Gagic 6
16SPENCERCarlos Fajelga6
17HILLSIDE Celso Oceana0
18HILLSIDE Chris San Juan6
19SPENCERConstant Ladreza6
20Danail Kazakov43
2148A SEACLIFFDaniele Mella0
22PRINCES STDanilo Dimacali0
23T BUILDDanilo Guevarra0
24MEADOWBANK PRIMARYDarko Miletic6
25M ONEDarko Radivojevic6
26HOLY TRINITY SCHOOLDarwin Marquez0
27RYMANDean Reynish0271123216221411
28SPENCEREddie Ismael6
29HILLSIDE Edgardo Cabamongan0
30M ONEEdmon Caisip6
31JERVOISElone Fotofili0
32HILLSIDE Emiliano Acoba0
33JERVOISEnrique Dimdim6
34CONSTELLATIONErano Obal6
35PRINCES STEric Celestial6
36QUEEN STFidel Mabanta0
37M ONEGilbert Manalili6
38HAUPAI SCHOOLGoran Arsovski6
39PRINCES STGwendolyn Nixon-Tamatea0
40BRAND PARTNERSIurii Makarov0
41Ivan Vulovic0
42HAUPAI SCHOOLIvan Zdravkovic0
43JERVOISJerico Dela Cruz0
44JERVOISJerry Calicdan0
45ELM PARKJesse Bailey0
46HILLSIDE Joebert Dizon6
47M ONEJohn Roy Corringham6
48SPENCERJose Monleon6
49SPENCERJoseph Reyes0
50CONSTELLATIONJuanito Crisostomo0
51M ONEKarl Ziento6
52HILLSIDE Kristoffer Dela Cruz0
53CONSTELLATIONLorenzo Mella0
54WAINUI SCHOOLLouie Carlos6
55HILLSIDE Macario Cortezano6
56Manraj Saroya43
57WAINUI SCHOOLMarco Stojiljkovic6
58M ONEMarnold Solejon0
59HILLSIDE Massimiliano Pratino0
60SPENCERMichael Marquez6
61CONSTELLATIONMiku Torralba0
62QUEEN STMilos Premovic0
63BRAND PARTNERSMiodrag Puvalic0
64ELM PARKOrlly Oyao0
65HILLSIDE Paul Vailua0
66ROLEY BOLTONPeter Ford0
67SPENCERRamil Reyes6
68STARBUCKSRamon Mariano6
69PARNELLRene Otsatalu0
70HILLSIDE Rey Pajo6
71WAINUI SCHOOLRodel Villarina0
72BENEFITZRolando Colina0
73SPENCERRonilo Lavesarez6
74M ONERuan Moller6
75HILLSIDE Samiu Toki6
76PARNELLSamuel Phillips0
77BRAND PARTNERSSead Asani6
78M ONESean Brosnan6
79JAZ'S HOUSESelimir Vasovic0
80HILLSIDE Shannan Wilson6
81WAINUI SCHOOLSlavko Jovanovic6
82MEADOWBANK PRIMARYSrdjan Cebic0
83HILLSIDE Taifeni Latu6
84JAZ'S HOUSETamas Kozma6
85QUEEN STVladimir Djordjevic0
86HAUPAI SCHOOLVladimir Vasovic6
87PARNELLVladimir Zikic0
88PRINCES STWendy Tamatea0
89STARBUCKSZeljko Curcic6
Main
Cell Formulas
RangeFormula
E1E1=COUNTIF(F1:FO1,"*")
F2:G2,K2,O2,Q2:R2,P2:P5,M2:N3,L2:L7,J2:J3,I2:I4,H2:H3F2=FILTER(Sites[[Name]:[Name]],(Sites[[Current Site]:[Current Site]]=F1)*(Sites[[Colour]:[Colour]]=0))
E27E27=SUM(F27:AH27)
F27:R27F27=COUNTIF(F2:F25,"?*")
C2:C89C2=IsColoured
 

NVRensburg

Board Regular
Joined
Jul 1, 2014
Messages
76
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
ABCDNOPQRSTUV
1Current SiteNameColour2548A SEACLIFFBENEFITZBRAND PARTNERSCONSTELLATIONELM PARKHAUPAI SCHOOLHILLSIDE
2T BUILDAaron Anthony Pengelly0Daniele MellaRolando ColinaIurii Makarov#SPILL!Jesse BaileyIvan ZdravkovicCelso Oceana
3HILLSIDE Adrian Baclayo6#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
4HILLSIDE Alan Nazareno6#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
5NUMATAldrin Dacanay6#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
648A SEACLIFFAleksandar Savic6#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
7SPENCERAlex Devazas6#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
8JERVOISAlexander Salamat0#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
9HUGHES & TUKEAndrew Aceron0#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
10CONSTELLATIONAriel Narvarte6#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
11HOLY TRINITY SCHOOLArnold Bonachita0#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
12JERVOISArnold Llamera6#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
13HUGHES & TUKEBaldo Manguna0#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
14HILLSIDE Brandon Lesoa6#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
15PT ENGLAND PRIMARYBranislav Gagic 6#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
16SPENCERCarlos Fajelga6#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
17HILLSIDE Celso Oceana0#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
18HILLSIDE Chris San Juan6#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
19SPENCERConstant Ladreza6#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
20Danail Kazakov0#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
2148A SEACLIFFDaniele Mella0#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
22PRINCES STDanilo Dimacali0#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
23T BUILDDanilo Guevarra0#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
24MEADOWBANK PRIMARYDarko Miletic6#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
25M ONEDarko Radivojevic6#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
26HOLY TRINITY SCHOOLDarwin Marquez0#CALC!#CALC!#CALC!#SPILL!#CALC!#CALC!#CALC!
27RYMANDean Reynish0191110111
28SPENCEREddie Ismael6
29HILLSIDE Edgardo Cabamongan0
30M ONEEdmon Caisip6
31JERVOISElone Fotofili0
32HILLSIDE Emiliano Acoba0
33JERVOISEnrique Dimdim6
34CONSTELLATIONErano Obal6
35PRINCES STEric Celestial6
36QUEEN STFidel Mabanta0
37M ONEGilbert Manalili6
38HAUPAI SCHOOLGoran Arsovski6
39PRINCES STGwendolyn Nixon-Tamatea0
40BRAND PARTNERSIurii Makarov0
41Ivan Vulovic6
42HAUPAI SCHOOLIvan Zdravkovic0
43JERVOISJerico Dela Cruz0
44JERVOISJerry Calicdan0
45ELM PARKJesse Bailey0
46HILLSIDE Joebert Dizon6
47M ONEJohn Roy Corringham6
48SPENCERJose Monleon6
49SPENCERJoseph Reyes0
50CONSTELLATIONJuanito Crisostomo0
51M ONEKarl Ziento6
52HILLSIDE Kristoffer Dela Cruz0
53CONSTELLATIONLorenzo Mella0
54WAINUI SCHOOLLouie Carlos6
55HILLSIDE Macario Cortezano6
56Manraj Saroya0
57WAINUI SCHOOLMarco Stojiljkovic6
58M ONEMarnold Solejon0
59HILLSIDE Massimiliano Pratino0
60SPENCERMichael Marquez6
61CONSTELLATIONMiku Torralba0
62QUEEN STMilos Premovic0
63BRAND PARTNERSMiodrag Puvalic6
64ELM PARKOrlly Oyao0
65HILLSIDE Paul Vailua0
66ROLEY BOLTONPeter Ford0
67SPENCERRamil Reyes6
68STARBUCKSRamon Mariano6
69PARNELLRene Otsatalu0
70HILLSIDE Rey Pajo6
71WAINUI SCHOOLRodel Villarina0
72BENEFITZRolando Colina0
73SPENCERRonilo Lavesarez6
74M ONERuan Moller6
75HILLSIDE Samiu Toki6
76PARNELLSamuel Phillips0
77BRAND PARTNERSSead Asani6
78M ONESean Brosnan6
79JAZ'S HOUSESelimir Vasovic0
80HILLSIDE Shannan Wilson6
81WAINUI SCHOOLSlavko Jovanovic6
82MEADOWBANK PRIMARYSrdjan Cebic0
83HILLSIDE Taifeni Latu6
84JAZ'S HOUSETamas Kozma6
85QUEEN STVladimir Djordjevic0
86HAUPAI SCHOOLVladimir Vasovic6
87PARNELLVladimir Zikic0
88PRINCES STWendy Tamatea0
89STARBUCKSZeljko Curcic6
INSERT SITES HERE
Cell Formulas
RangeFormula
O1O1=COUNTIF(P1:GU1,"*")
P2,S2:S19P2=FILTER(SITES[[Name]:[Name]],(SITES[[Current Site]:[Current Site]]=P1)*(SITES[[Colour]:[Colour]]=0))
Q2:R2,T2:V19,P3:R26,S20:V26Q2=FILTER(SITES[[Name]:[Name]],(SITES[[Current Site]:[Current Site]]=Q1)*(SITES[[Colour]:[Colour]]=0))
O27O27=SUM(P27:BN27)
P27:V27P27=COUNTIF(P2:P25,"?*")
C2:C89C2=IsColoured
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
P2:AN26Cellcontains an errortextNO
Cells with Data Validation
CellAllowCriteria
A1:A89List=$F:$F
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,266
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 1, 2014
Messages
76
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCDNOPQRSTUV
1Current SiteNameColour2548A SEACLIFFBENEFITZBRAND PARTNERSCONSTELLATIONELM PARKHAUPAI SCHOOLHILLSIDE
2T BUILDAaron Anthony Pengelly0Daniele MellaRolando ColinaIurii Makarov#SPILL!#SPILL!Ivan Zdravkovic#SPILL!
3HILLSIDE Adrian Baclayo6#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
4HILLSIDE Alan Nazareno6#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
5NUMATAldrin Dacanay6#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
648A SEACLIFFAleksandar Savic6#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
7SPENCERAlex Devazas6#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
8JERVOISAlexander Salamat0#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
9HUGHES & TUKEAndrew Aceron0#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
10CONSTELLATIONAriel Narvarte6#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
11HOLY TRINITY SCHOOLArnold Bonachita0#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
12JERVOISArnold Llamera6#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
13HUGHES & TUKEBaldo Manguna0#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
14HILLSIDE Brandon Lesoa6#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
15PT ENGLAND PRIMARYBranislav Gagic 6#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
16SPENCERCarlos Fajelga6#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
17HILLSIDE Celso Oceana0#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
18HILLSIDE Chris San Juan6#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
19SPENCERConstant Ladreza6#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
20Danail Kazakov0#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
2148A SEACLIFFDaniele Mella0#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
22PRINCES STDanilo Dimacali0#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
23T BUILDDanilo Guevarra0#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
24MEADOWBANK PRIMARYDarko Miletic6#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
25M ONEDarko Radivojevic6#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
26HOLY TRINITY SCHOOLDarwin Marquez0#CALC!#CALC!#CALC!#SPILL!#SPILL!#CALC!#SPILL!
27RYMANDean Reynish0161110010
28SPENCEREddie Ismael6
29HILLSIDE Edgardo Cabamongan0
30M ONEEdmon Caisip6
31JERVOISElone Fotofili0
32HILLSIDE Emiliano Acoba0
33JERVOISEnrique Dimdim6
34CONSTELLATIONErano Obal6
35PRINCES STEric Celestial6
36QUEEN STFidel Mabanta0
37M ONEGilbert Manalili6
38HAUPAI SCHOOLGoran Arsovski6
39PRINCES STGwendolyn Nixon-Tamatea0
40BRAND PARTNERSIurii Makarov0
41Ivan Vulovic6
42HAUPAI SCHOOLIvan Zdravkovic0
43JERVOISJerico Dela Cruz0
44JERVOISJerry Calicdan0
45ELM PARKJesse Bailey0
46HILLSIDE Joebert Dizon6
47M ONEJohn Roy Corringham6
48SPENCERJose Monleon6
49SPENCERJoseph Reyes0
50CONSTELLATIONJuanito Crisostomo0
51M ONEKarl Ziento6
52HILLSIDE Kristoffer Dela Cruz0
53CONSTELLATIONLorenzo Mella0
54WAINUI SCHOOLLouie Carlos6
55HILLSIDE Macario Cortezano6
56Manraj Saroya0
57WAINUI SCHOOLMarco Stojiljkovic6
58M ONEMarnold Solejon0
59HILLSIDE Massimiliano Pratino0
60SPENCERMichael Marquez6
61CONSTELLATIONMiku Torralba0
62QUEEN STMilos Premovic0
63BRAND PARTNERSMiodrag Puvalic6
64ELM PARKOrlly Oyao0
65HILLSIDE Paul Vailua0
66ROLEY BOLTONPeter Ford0
67SPENCERRamil Reyes6
68STARBUCKSRamon Mariano6
69PARNELLRene Otsatalu0
70HILLSIDE Rey Pajo6
71WAINUI SCHOOLRodel Villarina0
72BENEFITZRolando Colina0
73SPENCERRonilo Lavesarez6
74M ONERuan Moller6
75HILLSIDE Samiu Toki6
76PARNELLSamuel Phillips0
77BRAND PARTNERSSead Asani6
78M ONESean Brosnan6
79JAZ'S HOUSESelimir Vasovic0
80HILLSIDE Shannan Wilson6
81WAINUI SCHOOLSlavko Jovanovic6
82MEADOWBANK PRIMARYSrdjan Cebic0
83HILLSIDE Taifeni Latu6
84JAZ'S HOUSETamas Kozma6
85QUEEN STVladimir Djordjevic0
86HAUPAI SCHOOLVladimir Vasovic6
87PARNELLVladimir Zikic0
88PRINCES STWendy Tamatea0
89STARBUCKSZeljko Curcic6
INSERT SITES HERE
Cell Formulas
RangeFormula
O1O1=COUNTIF(P1:GU1,"*")
P2:V2,S3:S19P2=FILTER(SITES[[Name]:[Name]],(SITES[[Current Site]:[Current Site]]=P1)*(SITES[[Colour]:[Colour]]=0))
T3:V19,P3:R26,S20:V26P3=FILTER(SITES[[Name]:[Name]],(SITES[[Current Site]:[Current Site]]=P2)*(SITES[[Colour]:[Colour]]=0))
O27O27=SUM(P27:BN27)
P27:V27P27=COUNTIF(P2:P25,"?*")
C2:C89C2=IsColoured
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
P2:AN26Cellcontains an errortextNO
Cells with Data Validation
CellAllowCriteria
A1:A89List=$F:$F
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,266
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,235
Messages
5,600,467
Members
414,382
Latest member
kevinlarey

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