Let formula?

Lacan

Board Regular
Joined
Oct 5, 2016
Messages
228
Office Version
  1. 365
Platform
  1. Windows
Hi Guys, šŸ‘Œ

Would like help to complete Let formula in sheet.
In column D would like to Count Number of Names accordingly.
Any question please let me know.
Thank you very much! šŸ™šŸ‘šŸ‘

PLANEAMENTO_18.AGO.xlsx
ABCDEFGHIJKLM
1
2BrickBrickNameEspecialidadeCenter
3363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)301 Lx - AMA (Venteira)354 St - ABC (CPiedade)DELFINA MEDICINA GERAL E FAMILIARHospital PARTICULAR DE ALMADA
4308 Lx - XPTO (Benfica - Sul)333 Lx - XPTO (Carnide)MARIA MEDICINA GERAL E FAMILIARHOSPITAL DA LUZ LISBOA
5CenterEspecialidadeCount Number of Names315 Lx - XPTO (SAntĆ³nio - Este)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)LEONORMEDICINA GERAL E FAMILIARUSF BAIA AZUL (ULS ARRABIDA EPE)
6CH SETUBAL EPE - HOSPITAL DE SAO BERNARDOMEDICINA GERAL E FAMILIAR#REF!316 Lx - XPTO (SMMaior)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)ANNACARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
7USF BAIA AZUL (ULS ARRABIDA EPE)MEDICINA GERAL E FAMILIAR#REF!332 Lx - XPTO (SDBenfica - Norte)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)JOHNCARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
8USF BAIA AZUL (ULS ARRABIDA EPE)CARDIOLOGIA#REF!333 Lx - XPTO (Carnide)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)DENISEGASTROUSF BAIA AZUL (ULS ARRABIDA EPE)
9USF BAIA AZUL (ULS ARRABIDA EPE)GASTRO#REF!333 Lx - XPTO (SDBenfica - Norte)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)PAULNEUROUSF BAIA AZUL (ULS ARRABIDA EPE)
10USF BAIA AZUL (ULS ARRABIDA EPE)NEURO#REF!334 Lx - XPTO (SDBenfica - Norte)308 Lx - XPTO (Benfica - Sul)MARIA LUISAMEDICINA GERAL E FAMILIARUSF GERACOES (UCSP BENFICA)
11354 St - ABC (CPiedade)362 St - SET (SJuliĆ£o, SMGraƧa)MANUELMEDICINA GERAL E FAMILIARSAMS - POSTO CLINICO REGIONAL DE SETUBAL
12357 St - ABC (Caparica, Trafaria)315 Lx - XPTO (SAntĆ³nio - Este)AUGUSTOMEDICINA INTERNACH LISBOA CENTRAL EPE - HOSPITAL STO ANTONIO DOS CAPUCHOS
13361 St - SET (NSAnunciada, SLourenƧo, SSimĆ£o)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)ISABEL MEDICINA GERAL E FAMILIARCH SETUBAL EPE - HOSPITAL DE SAO BERNARDO
14362 St - SET (SJuliĆ£o, SMGraƧa)301 Lx - AMA (Venteira)GILBERTO MEDICINA GERAL E FAMILIARHOSPITAL DR FERNANDO FONSECA EPE (AMADORA / SINTRA)
15363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)362 St - SET (SJuliĆ£o, SMGraƧa)VITOR MEDICINA GERAL E FAMILIARPOSTO CLINICO PSP / SETUBAL
16362 St - SET (SJuliĆ£o, SMGraƧa)JOSE MEDICINA GERAL E FAMILIARConsultorio DR. BOCAGE
17315 Lx - XPTO (SAntĆ³nio - Este)ANTONIOCARDIOLOGIACH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
18332 Lx - XPTO (SDBenfica - Norte)JOAOREUMATOLOGIAHOSPITAL DA LUZ TORRES DE LISBOA
19333 Lx - XPTO (SDBenfica - Norte)LEO MEDICINA GERAL E FAMILIARHOSPITAL DA LUZ TORRES DE LISBOA
20334 Lx - XPTO (SDBenfica - Norte)JANNEMEDICINA GERAL E FAMILIARHOSPITAL DA LUZ TORRES DE LISBOA
21315 Lx - XPTO (SAntĆ³nio - Este)ANABELA CARDIOLOGIACH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
22361 St - SET (NSAnunciada, SLourenƧo, SSimĆ£o)ALBERTO MANUELMEDICINA GERAL E FAMILIARHOSPITAL DA LUZ CLINICA LUISA TODI
23357 St - ABC (Caparica, Trafaria)ANTONIO MANUELMEDICINA GERAL E FAMILIARUSF VISTA TEJO (CS COSTA CAPARICA)
24316 Lx - XPTO (SMMaior)MANUEL FERNANDO CARDIOLOGIAClĆ­nica SOCORROS MUTUOS EMPREGADOS COMERCIO INDUSTRIA
25333 Lx - XPTO (Carnide)LUIS FILIPECARDIOLOGIAHOSPITAL DA LUZ LISBOA
26
27
28
29
30
31
32
Folha1
Cell Formulas
RangeFormula
H3:H15H3=SORT(UNIQUE(I3:I25))
B6:D10B6=LET( brick,I3:I27, center,L3:L27, especialidade,K3:K27,name,J3:J27,f,UNIQUE(FILTER(CHOOSE({2,3},brick,center,especialidade),(brick=B3))), ub,INDEX(f,0,1), uc,INDEX(f,0,2), ue,INDEX(f,0,3), somaespecialidade,COUNTIFS(especialidade,"*"),SORT(CHOOSE({1,2,3},ub,uc,ue),1))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B3List=$H$3#
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
If I understand correctly, try:

Excel Formula:
=LET(
brick,I3:I27,
center,L3:L27,
especialidade,K3:K27,
name,J3:J27,
f,UNIQUE(FILTER(CHOOSE({2,3},brick,center,especialidade),brick=B3)),
ub,INDEX(f,0,1),
uc,INDEX(f,0,2),
somaespecialidade,COUNTIFS(center,ub,especialidade,uc),
SORT(CHOOSE({1,2,3},ub,uc,somaespecialidade),1))

in B6.
 
Upvote 0
If I understand correctly, try:

Excel Formula:
=LET(
brick,I3:I27,
center,L3:L27,
especialidade,K3:K27,
name,J3:J27,
f,UNIQUE(FILTER(CHOOSE({2,3},brick,center,especialidade),brick=B3)),
ub,INDEX(f,0,1),
uc,INDEX(f,0,2),
somaespecialidade,COUNTIFS(center,ub,especialidade,uc),
SORT(CHOOSE({1,2,3},ub,uc,somaespecialidade),1))

in B6.

Dear @Eric W

Thank you very because through your formula just made a few changes and got the results that was looking for.
However would like some upgrade.
Keeping the same formula can you make an upgrade to bring also the results in column E and column F.
Any question please let me know.
Very grateful! šŸ‘šŸ‘šŸ’ŖšŸ»šŸ»

PLANEAMENTO_18.AGO.xlsx
ABCDEFGHIJKLM
1
2BrickBrickNameEspecialidadeCenter
3363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)301 Lx - AMA (Venteira)354 St - ABC (CPiedade)DELFINA MEDICINA GERAL E FAMILIARHospital PARTICULAR DE ALMADA
4308 Lx - XPTO (Benfica - Sul)333 Lx - XPTO (Carnide)MARIA MEDICINA GERAL E FAMILIARHOSPITAL DA LUZ LISBOA
5CenterEspecialidadeCount Number of NamesCenterCount Number of Names315 Lx - XPTO (SAntĆ³nio - Este)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)LEONORMEDICINA GERAL E FAMILIARUSF BAIA AZUL (ULS ARRABIDA EPE)
6CH SETUBAL EPE - HOSPITAL DE SAO BERNARDOMEDICINA GERAL E FAMILIAR1CH SETUBAL EPE - HOSPITAL DE SAO BERNARDO1316 Lx - XPTO (SMMaior)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)ANNACARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
7USF BAIA AZUL (ULS ARRABIDA EPE)MEDICINA GERAL E FAMILIAR1USF BAIA AZUL (ULS ARRABIDA EPE)5332 Lx - XPTO (SDBenfica - Norte)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)JOHNCARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
8USF BAIA AZUL (ULS ARRABIDA EPE)CARDIOLOGIA3333 Lx - XPTO (Carnide)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)DENISEGASTROUSF BAIA AZUL (ULS ARRABIDA EPE)
9USF BAIA AZUL (ULS ARRABIDA EPE)GASTRO1333 Lx - XPTO (SDBenfica - Norte)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)PAULCARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
10334 Lx - XPTO (SDBenfica - Norte)308 Lx - XPTO (Benfica - Sul)MARIA LUISAMEDICINA GERAL E FAMILIARUSF GERACOES (UCSP BENFICA)
11354 St - ABC (CPiedade)362 St - SET (SJuliĆ£o, SMGraƧa)MANUELMEDICINA GERAL E FAMILIARSAMS - POSTO CLINICO REGIONAL DE SETUBAL
12357 St - ABC (Caparica, Trafaria)315 Lx - XPTO (SAntĆ³nio - Este)AUGUSTOCARDIOLOGIACH LISBOA CENTRAL EPE - HOSPITAL STO ANTONIO DOS CAPUCHOS
13361 St - SET (NSAnunciada, SLourenƧo, SSimĆ£o)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)ISABEL MEDICINA GERAL E FAMILIARCH SETUBAL EPE - HOSPITAL DE SAO BERNARDO
14362 St - SET (SJuliĆ£o, SMGraƧa)301 Lx - AMA (Venteira)GILBERTO MEDICINA GERAL E FAMILIARHOSPITAL DR FERNANDO FONSECA EPE (AMADORA / SINTRA)
15363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)362 St - SET (SJuliĆ£o, SMGraƧa)VITOR MEDICINA GERAL E FAMILIARPOSTO CLINICO PSP / SETUBAL
16362 St - SET (SJuliĆ£o, SMGraƧa)JOSE MEDICINA GERAL E FAMILIARConsultorio DR. BOCAGE
17315 Lx - XPTO (SAntĆ³nio - Este)ANTONIOCARDIOLOGIACH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
18332 Lx - XPTO (SDBenfica - Norte)JOAOREUMATOLOGIAHOSPITAL DA LUZ TORRES DE LISBOA
19333 Lx - XPTO (SDBenfica - Norte)LEO MEDICINA GERAL E FAMILIARHOSPITAL DA LUZ TORRES DE LISBOA
20334 Lx - XPTO (SDBenfica - Norte)JANNEMEDICINA GERAL E FAMILIARHOSPITAL DA LUZ TORRES DE LISBOA
21315 Lx - XPTO (SAntĆ³nio - Este)ANABELA MED INTERNACH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
22361 St - SET (NSAnunciada, SLourenƧo, SSimĆ£o)ALBERTO MANUELMEDICINA GERAL E FAMILIARHOSPITAL DA LUZ CLINICA LUISA TODI
23357 St - ABC (Caparica, Trafaria)ANTONIO MANUELMEDICINA GERAL E FAMILIARUSF VISTA TEJO (CS COSTA CAPARICA)
24316 Lx - XPTO (SMMaior)MANUEL FERNANDO CARDIOLOGIAClĆ­nica SOCORROS MUTUOS EMPREGADOS COMERCIO INDUSTRIA
25333 Lx - XPTO (Carnide)LUIS FILIPECARDIOLOGIAHOSPITAL DA LUZ LISBOA
26
27
Folha1
Cell Formulas
RangeFormula
H3:H15H3=SORT(UNIQUE(I3:I25))
B6:D9B6=LET( brick,I3:I27, center,L3:L27, especialidade,K3:K27,name,J3:J27,f,UNIQUE(FILTER(CHOOSE({2,3},brick,center,especialidade),(brick=B3))), ub,INDEX(f,0,1), uc,INDEX(f,0,2), ue,INDEX(f,0,3), somaespecialidade,COUNTIFS(center,ub,especialidade,uc),SORT(CHOOSE({1,2,3},ub,uc,somaespecialidade),1))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B3List=$H$3#
 
Upvote 0
While it would be possible to combine that into a single formula, I think it makes more sense to just create another formula, especially due to the fact that there will be a different number of rows in the output. Try:

Book1
ABCDEF
1
2Brick
3363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)
4
5CenterEspecialidadeCount Number of NamesCenterCount Number of Names
6CH SETUBAL EPE - HOSPITAL DE SAO BERNARDOMEDICINA GERAL E FAMILIAR1CH SETUBAL EPE - HOSPITAL DE SAO BERNARDO1
7USF BAIA AZUL (ULS ARRABIDA EPE)MEDICINA GERAL E FAMILIAR1USF BAIA AZUL (ULS ARRABIDA EPE)5
8USF BAIA AZUL (ULS ARRABIDA EPE)CARDIOLOGIA3
9USF BAIA AZUL (ULS ARRABIDA EPE)GASTRO1
10
Sheet8
Cell Formulas
RangeFormula
B6:D9B6=LET( brick,I3:I27, center,L3:L27, especialidade,K3:K27,name,J3:J27,f,UNIQUE(FILTER(CHOOSE({2,3},brick,center,especialidade),(brick=B3))), ub,INDEX(f,0,1), uc,INDEX(f,0,2), ue,INDEX(f,0,3), somaespecialidade,COUNTIFS(center,ub,especialidade,uc),SORT(CHOOSE({1,2,3},ub,uc,somaespecialidade),1))
E6:F7E6=LET(c,INDEX(B6#,0,1),n,INDEX(B6#,0,3),u,UNIQUE(c),CHOOSE({1,2},u,SUMIFS(n,c,u)))
Dynamic array formulas.


If you really want it in a single formula, let me know.
 
Upvote 0
While it would be possible to combine that into a single formula, I think it makes more sense to just create another formula, especially due to the fact that there will be a different number of rows in the output. Try:

Book1
ABCDEF
1
2Brick
3363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)
4
5CenterEspecialidadeCount Number of NamesCenterCount Number of Names
6CH SETUBAL EPE - HOSPITAL DE SAO BERNARDOMEDICINA GERAL E FAMILIAR1CH SETUBAL EPE - HOSPITAL DE SAO BERNARDO1
7USF BAIA AZUL (ULS ARRABIDA EPE)MEDICINA GERAL E FAMILIAR1USF BAIA AZUL (ULS ARRABIDA EPE)5
8USF BAIA AZUL (ULS ARRABIDA EPE)CARDIOLOGIA3
9USF BAIA AZUL (ULS ARRABIDA EPE)GASTRO1
10
Sheet8
Cell Formulas
RangeFormula
B6:D9B6=LET( brick,I3:I27, center,L3:L27, especialidade,K3:K27,name,J3:J27,f,UNIQUE(FILTER(CHOOSE({2,3},brick,center,especialidade),(brick=B3))), ub,INDEX(f,0,1), uc,INDEX(f,0,2), ue,INDEX(f,0,3), somaespecialidade,COUNTIFS(center,ub,especialidade,uc),SORT(CHOOSE({1,2,3},ub,uc,somaespecialidade),1))
E6:F7E6=LET(c,INDEX(B6#,0,1),n,INDEX(B6#,0,3),u,UNIQUE(c),CHOOSE({1,2},u,SUMIFS(n,c,u)))
Dynamic array formulas.


If you really want it in a single formula, let me know.
Dear @Eric W

Yes please, can you add in a unique single formula?
Thanks again!šŸ”šŸ‘ŒšŸ‘
 
Upvote 0
Try:

Excel Formula:
=LET(
brick,I3:I27,
center,L3:L27,
especialidade,K3:K27,
br,B3,
f,SORT(UNIQUE(FILTER(CHOOSE({1,2},center,especialidade),brick=br)),1),
ub,INDEX(f,0,1),
uc,INDEX(f,0,2),
somaespecialidade,COUNTIFS(center,ub,especialidade,uc),
ctr,UNIQUE(ub),
ctrno,COUNTIFS(brick,br,center,ctr),
IFERROR(CHOOSE({1,2,3,4,5},ub,uc,somaespecialidade,ctr,ctrno),""))
 
Upvote 0
Dear @Eric W

It returns all blank.
In order to help just notice that range column name is not included.
Can you please give a look?
Thanks again.
In B16.

PLANEAMENTO_18.AGO.xlsx
ABCDEFGHIJKLMN
1
2BrickBrickNameEspecialidadeCenter
3363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)301 Lx - AMA (Venteira)354 St - ABC (CPiedade)DELFINA MEDICINA GERAL E FAMILIARHospital PARTICULAR DE ALMADA
4308 Lx - XPTO (Benfica - Sul)333 Lx - XPTO (Carnide)MARIA MEDICINA GERAL E FAMILIARHOSPITAL DA LUZ LISBOA
5CenterEspecialidadeCount Number of NamesCenterCount Number of Names315 Lx - XPTO (SAntĆ³nio - Este)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)LEONORMEDICINA GERAL E FAMILIARUSF BAIA AZUL (ULS ARRABIDA EPE)
6CH SETUBAL EPE - HOSPITAL DE SAO BERNARDOMEDICINA GERAL E FAMILIAR1CH SETUBAL EPE - HOSPITAL DE SAO BERNARDO1316 Lx - XPTO (SMMaior)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)ANNACARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
7USF BAIA AZUL (ULS ARRABIDA EPE)MEDICINA GERAL E FAMILIAR1USF BAIA AZUL (ULS ARRABIDA EPE)5332 Lx - XPTO (SDBenfica - Norte)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)JOHNCARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
8USF BAIA AZUL (ULS ARRABIDA EPE)CARDIOLOGIA3333 Lx - XPTO (Carnide)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)DENISEGASTROUSF BAIA AZUL (ULS ARRABIDA EPE)
9USF BAIA AZUL (ULS ARRABIDA EPE)GASTRO1333 Lx - XPTO (SDBenfica - Norte)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)PAULCARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
10334 Lx - XPTO (SDBenfica - Norte)308 Lx - XPTO (Benfica - Sul)MARIA LUISAMEDICINA GERAL E FAMILIARUSF GERACOES (UCSP BENFICA)
11354 St - ABC (CPiedade)362 St - SET (SJuliĆ£o, SMGraƧa)MANUELMEDICINA GERAL E FAMILIARSAMS - POSTO CLINICO REGIONAL DE SETUBAL
12357 St - ABC (Caparica, Trafaria)315 Lx - XPTO (SAntĆ³nio - Este)AUGUSTOCARDIOLOGIACH LISBOA CENTRAL EPE - HOSPITAL STO ANTONIO DOS CAPUCHOS
13361 St - SET (NSAnunciada, SLourenƧo, SSimĆ£o)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)ISABEL MEDICINA GERAL E FAMILIARCH SETUBAL EPE - HOSPITAL DE SAO BERNARDO
14362 St - SET (SJuliĆ£o, SMGraƧa)301 Lx - AMA (Venteira)GILBERTO MEDICINA GERAL E FAMILIARHOSPITAL DR FERNANDO FONSECA EPE (AMADORA / SINTRA)
15CenterEspecialidadeCount Number of NamesCenterCount Number of Names363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)362 St - SET (SJuliĆ£o, SMGraƧa)VITOR MEDICINA GERAL E FAMILIARPOSTO CLINICO PSP / SETUBAL
16 362 St - SET (SJuliĆ£o, SMGraƧa)JOSE MEDICINA GERAL E FAMILIARConsultorio DR. BOCAGE
17315 Lx - XPTO (SAntĆ³nio - Este)ANTONIOCARDIOLOGIACH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
180332 Lx - XPTO (SDBenfica - Norte)JOAOREUMATOLOGIAHOSPITAL DA LUZ TORRES DE LISBOA
19333 Lx - XPTO (SDBenfica - Norte)LEO MEDICINA GERAL E FAMILIARHOSPITAL DA LUZ TORRES DE LISBOA
200334 Lx - XPTO (SDBenfica - Norte)JANNEMEDICINA GERAL E FAMILIARHOSPITAL DA LUZ TORRES DE LISBOA
21315 Lx - XPTO (SAntĆ³nio - Este)ANABELA MED INTERNACH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
22361 St - SET (NSAnunciada, SLourenƧo, SSimĆ£o)ALBERTO MANUELMEDICINA GERAL E FAMILIARHOSPITAL DA LUZ CLINICA LUISA TODI
23357 St - ABC (Caparica, Trafaria)ANTONIO MANUELMEDICINA GERAL E FAMILIARUSF VISTA TEJO (CS COSTA CAPARICA)
24316 Lx - XPTO (SMMaior)MANUEL FERNANDO CARDIOLOGIAClĆ­nica SOCORROS MUTUOS EMPREGADOS COMERCIO INDUSTRIA
25333 Lx - XPTO (Carnide)LUIS FILIPECARDIOLOGIAHOSPITAL DA LUZ LISBOA
26
27
28
29
Folha1 (2)
Cell Formulas
RangeFormula
H3:H15H3=SORT(UNIQUE(I3:I25))
B6:D9B6=LET( brick,I3:I27, center,L3:L27, especialidade,K3:K27,name,J3:J27,f,UNIQUE(FILTER(CHOOSE({2,3},brick,center,especialidade),(brick=B3))), ub,INDEX(f,0,1), uc,INDEX(f,0,2), ue,INDEX(f,0,3), somaespecialidade,COUNTIFS(center,ub,especialidade,uc),SORT(CHOOSE({1,2,3},ub,uc,somaespecialidade),1))
B16:B20B16=LET(brick,I3:I27,center,L3:L27,especialidade,K3:K27,br,B3,f,SORT(UNIQUE(FILTER(CHOOSE({1;2},center,especialidade),brick=br)),1),ub,INDEX(f,0,1),uc,INDEX(f,0,2),somaespecialidade,COUNTIFS(center,ub,especialidade,uc),ctr,UNIQUE(ub),ctrno,COUNTIFS(brick,br,center,ctr),IFERROR(CHOOSE({1;2;3;4;5},ub,uc,somaespecialidade,ctr,ctrno),""))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B3List=$H$3#
 
Upvote 0
The formula returned blanks because you changed the commas in the array constants to semicolons ({1,2} was changed to {1;2} and {1,2,3,4,5} was changed to {1;2;3;4;5}). The names column was not explicitly defined because you're just looking for a count, not the actual names. We can determine the count based on the rows that were in the filter. If there were duplicate names, this might not be true, but there were no duplicates in the example.

Book1
ABCDEFGHIJKL
1
2BrickBrickNameEspecialidadeCenter
3363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)301 Lx - AMA (Venteira)354 St - ABC (CPiedade)DELFINA MEDICINA GERAL E FAMILIARHospital PARTICULAR DE ALMADA
4308 Lx - XPTO (Benfica - Sul)333 Lx - XPTO (Carnide)MARIA MEDICINA GERAL E FAMILIARHOSPITAL DA LUZ LISBOA
5CenterEspecialidadeCount Number of NamesCenterCount Number of Names315 Lx - XPTO (SAntĆ³nio - Este)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)LEONORMEDICINA GERAL E FAMILIARUSF BAIA AZUL (ULS ARRABIDA EPE)
6CH SETUBAL EPE - HOSPITAL DE SAO BERNARDOMEDICINA GERAL E FAMILIAR1CH SETUBAL EPE - HOSPITAL DE SAO BERNARDO1316 Lx - XPTO (SMMaior)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)ANNACARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
7USF BAIA AZUL (ULS ARRABIDA EPE)MEDICINA GERAL E FAMILIAR1USF BAIA AZUL (ULS ARRABIDA EPE)5332 Lx - XPTO (SDBenfica - Norte)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)JOHNCARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
8USF BAIA AZUL (ULS ARRABIDA EPE)CARDIOLOGIA3333 Lx - XPTO (Carnide)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)DENISEGASTROUSF BAIA AZUL (ULS ARRABIDA EPE)
9USF BAIA AZUL (ULS ARRABIDA EPE)GASTRO1333 Lx - XPTO (SDBenfica - Norte)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)PAULCARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
10334 Lx - XPTO (SDBenfica - Norte)308 Lx - XPTO (Benfica - Sul)MARIA LUISAMEDICINA GERAL E FAMILIARUSF GERACOES (UCSP BENFICA)
11354 St - ABC (CPiedade)362 St - SET (SJuliĆ£o, SMGraƧa)MANUELMEDICINA GERAL E FAMILIARSAMS - POSTO CLINICO REGIONAL DE SETUBAL
12357 St - ABC (Caparica, Trafaria)315 Lx - XPTO (SAntĆ³nio - Este)AUGUSTOCARDIOLOGIACH LISBOA CENTRAL EPE - HOSPITAL STO ANTONIO DOS CAPUCHOS
13361 St - SET (NSAnunciada, SLourenƧo, SSimĆ£o)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)ISABEL MEDICINA GERAL E FAMILIARCH SETUBAL EPE - HOSPITAL DE SAO BERNARDO
14362 St - SET (SJuliĆ£o, SMGraƧa)301 Lx - AMA (Venteira)GILBERTO MEDICINA GERAL E FAMILIARHOSPITAL DR FERNANDO FONSECA EPE (AMADORA / SINTRA)
15CenterEspecialidadeCount Number of NamesCenterCount Number of Names363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)362 St - SET (SJuliĆ£o, SMGraƧa)VITOR MEDICINA GERAL E FAMILIARPOSTO CLINICO PSP / SETUBAL
16CH SETUBAL EPE - HOSPITAL DE SAO BERNARDOMEDICINA GERAL E FAMILIAR1CH SETUBAL EPE - HOSPITAL DE SAO BERNARDO1362 St - SET (SJuliĆ£o, SMGraƧa)JOSE MEDICINA GERAL E FAMILIARConsultorio DR. BOCAGE
17USF BAIA AZUL (ULS ARRABIDA EPE)MEDICINA GERAL E FAMILIAR1USF BAIA AZUL (ULS ARRABIDA EPE)5315 Lx - XPTO (SAntĆ³nio - Este)ANTONIOCARDIOLOGIACH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
18USF BAIA AZUL (ULS ARRABIDA EPE)CARDIOLOGIA3332 Lx - XPTO (SDBenfica - Norte)JOAOREUMATOLOGIAHOSPITAL DA LUZ TORRES DE LISBOA
19USF BAIA AZUL (ULS ARRABIDA EPE)GASTRO1333 Lx - XPTO (SDBenfica - Norte)LEO MEDICINA GERAL E FAMILIARHOSPITAL DA LUZ TORRES DE LISBOA
20334 Lx - XPTO (SDBenfica - Norte)JANNEMEDICINA GERAL E FAMILIARHOSPITAL DA LUZ TORRES DE LISBOA
21315 Lx - XPTO (SAntĆ³nio - Este)ANABELA MED INTERNACH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
22361 St - SET (NSAnunciada, SLourenƧo, SSimĆ£o)ALBERTO MANUELMEDICINA GERAL E FAMILIARHOSPITAL DA LUZ CLINICA LUISA TODI
23357 St - ABC (Caparica, Trafaria)ANTONIO MANUELMEDICINA GERAL E FAMILIARUSF VISTA TEJO (CS COSTA CAPARICA)
24316 Lx - XPTO (SMMaior)MANUEL FERNANDO CARDIOLOGIAClĆ­nica SOCORROS MUTUOS EMPREGADOS COMERCIO INDUSTRIA
25333 Lx - XPTO (Carnide)LUIS FILIPECARDIOLOGIAHOSPITAL DA LUZ LISBOA
Sheet12
Cell Formulas
RangeFormula
H3:H15H3=SORT(UNIQUE(I3:I25))
B6:D9B6=LET( brick,I3:I27, center,L3:L27, especialidade,K3:K27,name,J3:J27,f,UNIQUE(FILTER(CHOOSE({2,3},brick,center,especialidade),(brick=B3))), ub,INDEX(f,0,1), uc,INDEX(f,0,2), ue,INDEX(f,0,3), somaespecialidade,COUNTIFS(center,ub,especialidade,uc),SORT(CHOOSE({1,2,3},ub,uc,somaespecialidade),1))
B16:F19B16=LET(brick,I3:I27,center,L3:L27,especialidade,K3:K27,br,B3,f,SORT(UNIQUE(FILTER(CHOOSE({1,2},center,especialidade),brick=br)),1),ub,INDEX(f,0,1),uc,INDEX(f,0,2),somaespecialidade,COUNTIFS(center,ub,especialidade,uc),ctr,UNIQUE(ub),ctrno,COUNTIFS(brick,br,center,ctr),IFERROR(CHOOSE({1,2,3,4,5},ub,uc,somaespecialidade,ctr,ctrno),""))
Dynamic array formulas.
 
Upvote 0
The formula returned blanks because you changed the commas in the array constants to semicolons ({1,2} was changed to {1;2} and {1,2,3,4,5} was changed to {1;2;3;4;5}). The names column was not explicitly defined because you're just looking for a count, not the actual names. We can determine the count based on the rows that were in the filter. If there were duplicate names, this might not be true, but there were no duplicates in the example.

Book1
ABCDEFGHIJKL
1
2BrickBrickNameEspecialidadeCenter
3363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)301 Lx - AMA (Venteira)354 St - ABC (CPiedade)DELFINA MEDICINA GERAL E FAMILIARHospital PARTICULAR DE ALMADA
4308 Lx - XPTO (Benfica - Sul)333 Lx - XPTO (Carnide)MARIA MEDICINA GERAL E FAMILIARHOSPITAL DA LUZ LISBOA
5CenterEspecialidadeCount Number of NamesCenterCount Number of Names315 Lx - XPTO (SAntĆ³nio - Este)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)LEONORMEDICINA GERAL E FAMILIARUSF BAIA AZUL (ULS ARRABIDA EPE)
6CH SETUBAL EPE - HOSPITAL DE SAO BERNARDOMEDICINA GERAL E FAMILIAR1CH SETUBAL EPE - HOSPITAL DE SAO BERNARDO1316 Lx - XPTO (SMMaior)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)ANNACARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
7USF BAIA AZUL (ULS ARRABIDA EPE)MEDICINA GERAL E FAMILIAR1USF BAIA AZUL (ULS ARRABIDA EPE)5332 Lx - XPTO (SDBenfica - Norte)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)JOHNCARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
8USF BAIA AZUL (ULS ARRABIDA EPE)CARDIOLOGIA3333 Lx - XPTO (Carnide)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)DENISEGASTROUSF BAIA AZUL (ULS ARRABIDA EPE)
9USF BAIA AZUL (ULS ARRABIDA EPE)GASTRO1333 Lx - XPTO (SDBenfica - Norte)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)PAULCARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
10334 Lx - XPTO (SDBenfica - Norte)308 Lx - XPTO (Benfica - Sul)MARIA LUISAMEDICINA GERAL E FAMILIARUSF GERACOES (UCSP BENFICA)
11354 St - ABC (CPiedade)362 St - SET (SJuliĆ£o, SMGraƧa)MANUELMEDICINA GERAL E FAMILIARSAMS - POSTO CLINICO REGIONAL DE SETUBAL
12357 St - ABC (Caparica, Trafaria)315 Lx - XPTO (SAntĆ³nio - Este)AUGUSTOCARDIOLOGIACH LISBOA CENTRAL EPE - HOSPITAL STO ANTONIO DOS CAPUCHOS
13361 St - SET (NSAnunciada, SLourenƧo, SSimĆ£o)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)ISABEL MEDICINA GERAL E FAMILIARCH SETUBAL EPE - HOSPITAL DE SAO BERNARDO
14362 St - SET (SJuliĆ£o, SMGraƧa)301 Lx - AMA (Venteira)GILBERTO MEDICINA GERAL E FAMILIARHOSPITAL DR FERNANDO FONSECA EPE (AMADORA / SINTRA)
15CenterEspecialidadeCount Number of NamesCenterCount Number of Names363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)362 St - SET (SJuliĆ£o, SMGraƧa)VITOR MEDICINA GERAL E FAMILIARPOSTO CLINICO PSP / SETUBAL
16CH SETUBAL EPE - HOSPITAL DE SAO BERNARDOMEDICINA GERAL E FAMILIAR1CH SETUBAL EPE - HOSPITAL DE SAO BERNARDO1362 St - SET (SJuliĆ£o, SMGraƧa)JOSE MEDICINA GERAL E FAMILIARConsultorio DR. BOCAGE
17USF BAIA AZUL (ULS ARRABIDA EPE)MEDICINA GERAL E FAMILIAR1USF BAIA AZUL (ULS ARRABIDA EPE)5315 Lx - XPTO (SAntĆ³nio - Este)ANTONIOCARDIOLOGIACH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
18USF BAIA AZUL (ULS ARRABIDA EPE)CARDIOLOGIA3332 Lx - XPTO (SDBenfica - Norte)JOAOREUMATOLOGIAHOSPITAL DA LUZ TORRES DE LISBOA
19USF BAIA AZUL (ULS ARRABIDA EPE)GASTRO1333 Lx - XPTO (SDBenfica - Norte)LEO MEDICINA GERAL E FAMILIARHOSPITAL DA LUZ TORRES DE LISBOA
20334 Lx - XPTO (SDBenfica - Norte)JANNEMEDICINA GERAL E FAMILIARHOSPITAL DA LUZ TORRES DE LISBOA
21315 Lx - XPTO (SAntĆ³nio - Este)ANABELA MED INTERNACH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
22361 St - SET (NSAnunciada, SLourenƧo, SSimĆ£o)ALBERTO MANUELMEDICINA GERAL E FAMILIARHOSPITAL DA LUZ CLINICA LUISA TODI
23357 St - ABC (Caparica, Trafaria)ANTONIO MANUELMEDICINA GERAL E FAMILIARUSF VISTA TEJO (CS COSTA CAPARICA)
24316 Lx - XPTO (SMMaior)MANUEL FERNANDO CARDIOLOGIAClĆ­nica SOCORROS MUTUOS EMPREGADOS COMERCIO INDUSTRIA
25333 Lx - XPTO (Carnide)LUIS FILIPECARDIOLOGIAHOSPITAL DA LUZ LISBOA
Sheet12
Cell Formulas
RangeFormula
H3:H15H3=SORT(UNIQUE(I3:I25))
B6:D9B6=LET( brick,I3:I27, center,L3:L27, especialidade,K3:K27,name,J3:J27,f,UNIQUE(FILTER(CHOOSE({2,3},brick,center,especialidade),(brick=B3))), ub,INDEX(f,0,1), uc,INDEX(f,0,2), ue,INDEX(f,0,3), somaespecialidade,COUNTIFS(center,ub,especialidade,uc),SORT(CHOOSE({1,2,3},ub,uc,somaespecialidade),1))
B16:F19B16=LET(brick,I3:I27,center,L3:L27,especialidade,K3:K27,br,B3,f,SORT(UNIQUE(FILTER(CHOOSE({1,2},center,especialidade),brick=br)),1),ub,INDEX(f,0,1),uc,INDEX(f,0,2),somaespecialidade,COUNTIFS(center,ub,especialidade,uc),ctr,UNIQUE(ub),ctrno,COUNTIFS(brick,br,center,ctr),IFERROR(CHOOSE({1,2,3,4,5},ub,uc,somaespecialidade,ctr,ctrno),""))
Dynamic array formulas.

Dear @Eric W

You were right however translated to Portugue is "\" instead of ",". One more thing that have learned.
Is working completely however curiously when choose "333 Lx - XPTO (Carnide)" in B3 should only return one result of "HOSPITAL DA LUZ LISBOA 2" instead of the same double result.
All the results in other cells are correct.
Maybe is missing one more Unique function in formula.
Can you please give a look?
Thanks thanks again. šŸ‘šŸ‘

PLANEAMENTO_18.AGO.xlsx
ABCDEFGHIJKLM
1
2BrickBrickNameEspecialidadeCenter
3333 Lx - XPTO (Carnide)301 Lx - AMA (Venteira)354 St - ABC (CPiedade)DELFINA MEDICINA GERAL E FAMILIARHospital PARTICULAR DE ALMADA
4308 Lx - XPTO (Benfica - Sul)333 Lx - XPTO (Carnide)MARIA MEDICINA GERAL E FAMILIARHOSPITAL DA LUZ LISBOA
5CenterEspecialidadeCount Number of NamesCenterCount Number of Names315 Lx - XPTO (SAntĆ³nio - Este)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)LEONORMEDICINA GERAL E FAMILIARUSF BAIA AZUL (ULS ARRABIDA EPE)
6HOSPITAL DA LUZ LISBOAMEDICINA GERAL E FAMILIAR1316 Lx - XPTO (SMMaior)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)ANNACARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
7HOSPITAL DA LUZ LISBOACARDIOLOGIA1332 Lx - XPTO (SDBenfica - Norte)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)JOHNCARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
8333 Lx - XPTO (Carnide)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)DENISEGASTROUSF BAIA AZUL (ULS ARRABIDA EPE)
9333 Lx - XPTO (SDBenfica - Norte)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)PAULCARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
10334 Lx - XPTO (SDBenfica - Norte)308 Lx - XPTO (Benfica - Sul)MARIA LUISAMEDICINA GERAL E FAMILIARUSF GERACOES (UCSP BENFICA)
11354 St - ABC (CPiedade)362 St - SET (SJuliĆ£o, SMGraƧa)MANUELMEDICINA GERAL E FAMILIARSAMS - POSTO CLINICO REGIONAL DE SETUBAL
12357 St - ABC (Caparica, Trafaria)315 Lx - XPTO (SAntĆ³nio - Este)AUGUSTOCARDIOLOGIACH LISBOA CENTRAL EPE - HOSPITAL STO ANTONIO DOS CAPUCHOS
13361 St - SET (NSAnunciada, SLourenƧo, SSimĆ£o)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)ISABEL MEDICINA GERAL E FAMILIARCH SETUBAL EPE - HOSPITAL DE SAO BERNARDO
14CenterEspecialidadeCount Number of NamesCenterCount Number of Names362 St - SET (SJuliĆ£o, SMGraƧa)301 Lx - AMA (Venteira)GILBERTO MEDICINA GERAL E FAMILIARHOSPITAL DR FERNANDO FONSECA EPE (AMADORA / SINTRA)
15HOSPITAL DA LUZ LISBOAMEDICINA GERAL E FAMILIAR1HOSPITAL DA LUZ LISBOA2363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)362 St - SET (SJuliĆ£o, SMGraƧa)VITOR MEDICINA GERAL E FAMILIARPOSTO CLINICO PSP / SETUBAL
16HOSPITAL DA LUZ LISBOACARDIOLOGIA1HOSPITAL DA LUZ LISBOA2362 St - SET (SJuliĆ£o, SMGraƧa)JOSE MEDICINA GERAL E FAMILIARConsultorio DR. BOCAGE
17315 Lx - XPTO (SAntĆ³nio - Este)ANTONIOCARDIOLOGIACH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
18332 Lx - XPTO (SDBenfica - Norte)JOAOREUMATOLOGIAHOSPITAL DA LUZ TORRES DE LISBOA
19333 Lx - XPTO (SDBenfica - Norte)LEO MEDICINA GERAL E FAMILIARHOSPITAL DA LUZ TORRES DE LISBOA
20334 Lx - XPTO (SDBenfica - Norte)JANNEMEDICINA GERAL E FAMILIARHOSPITAL DA LUZ TORRES DE LISBOA
21315 Lx - XPTO (SAntĆ³nio - Este)ANABELA MED INTERNACH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
22361 St - SET (NSAnunciada, SLourenƧo, SSimĆ£o)ALBERTO MANUELMEDICINA GERAL E FAMILIARHOSPITAL DA LUZ CLINICA LUISA TODI
23357 St - ABC (Caparica, Trafaria)ANTONIO MANUELMEDICINA GERAL E FAMILIARUSF VISTA TEJO (CS COSTA CAPARICA)
24316 Lx - XPTO (SMMaior)MANUEL FERNANDO CARDIOLOGIAClĆ­nica SOCORROS MUTUOS EMPREGADOS COMERCIO INDUSTRIA
25333 Lx - XPTO (Carnide)LUIS FILIPECARDIOLOGIAHOSPITAL DA LUZ LISBOA
26
27
28
Folha1 (3)
Cell Formulas
RangeFormula
H3:H15H3=SORT(UNIQUE(I3:I25))
B6:D7B6=LET( brick,I3:I27, center,L3:L27, especialidade,K3:K27,name,J3:J27,f,UNIQUE(FILTER(CHOOSE({2,3},brick,center,especialidade),(brick=B3))), ub,INDEX(f,0,1), uc,INDEX(f,0,2), ue,INDEX(f,0,3), somaespecialidade,COUNTIFS(center,ub,especialidade,uc),SORT(CHOOSE({1,2,3},ub,uc,somaespecialidade),1))
B15:F16B15=LET(brick,I3:I27,center,L3:L27,especialidade,K3:K27,br,B3,f,SORT(UNIQUE(FILTER(CHOOSE({1,2},center,especialidade),brick=br)),1),ub,INDEX(f,0,1),uc,INDEX(f,0,2),somaespecialidade,COUNTIFS(center,ub,especialidade,uc),ctr,UNIQUE(ub),ctrno,COUNTIFS(brick,br,center,ctr),IFERROR(CHOOSE({1,2,3,4,5},ub,uc,somaespecialidade,ctr,ctrno),""))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
B3List=$H$3#
 
Upvote 0
The row and column separators (";" and "," in North America, "," and "\" in your region) are defined in the Regional Settings. I should have thought of that, but you've figured it out now. The latest problem is due to the CHOOSE having trouble concatenating a list with a single row - the single row is assumed to be a constant that should go in every row. I found a workaround that makes the last 2 columns into a list if necessary.

Book2
ABCDEFGHIJKL
1
2BrickBrickNameEspecialidadeCenter
3333 Lx - XPTO (Carnide)301 Lx - AMA (Venteira)354 St - ABC (CPiedade)DELFINA MEDICINA GERAL E FAMILIARHospital PARTICULAR DE ALMADA
4308 Lx - XPTO (Benfica - Sul)333 Lx - XPTO (Carnide)MARIA MEDICINA GERAL E FAMILIARHOSPITAL DA LUZ LISBOA
5CenterEspecialidadeCount Number of NamesCenterCount Number of Names315 Lx - XPTO (SAntĆ³nio - Este)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)LEONORMEDICINA GERAL E FAMILIARUSF BAIA AZUL (ULS ARRABIDA EPE)
6HOSPITAL DA LUZ LISBOAMEDICINA GERAL E FAMILIAR1316 Lx - XPTO (SMMaior)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)ANNACARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
7HOSPITAL DA LUZ LISBOACARDIOLOGIA1332 Lx - XPTO (SDBenfica - Norte)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)JOHNCARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
8333 Lx - XPTO (Carnide)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)DENISEGASTROUSF BAIA AZUL (ULS ARRABIDA EPE)
9333 Lx - XPTO (SDBenfica - Norte)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)PAULCARDIOLOGIAUSF BAIA AZUL (ULS ARRABIDA EPE)
10334 Lx - XPTO (SDBenfica - Norte)308 Lx - XPTO (Benfica - Sul)MARIA LUISAMEDICINA GERAL E FAMILIARUSF GERACOES (UCSP BENFICA)
11354 St - ABC (CPiedade)362 St - SET (SJuliĆ£o, SMGraƧa)MANUELMEDICINA GERAL E FAMILIARSAMS - POSTO CLINICO REGIONAL DE SETUBAL
12357 St - ABC (Caparica, Trafaria)315 Lx - XPTO (SAntĆ³nio - Este)AUGUSTOCARDIOLOGIACH LISBOA CENTRAL EPE - HOSPITAL STO ANTONIO DOS CAPUCHOS
13361 St - SET (NSAnunciada, SLourenƧo, SSimĆ£o)363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)ISABEL MEDICINA GERAL E FAMILIARCH SETUBAL EPE - HOSPITAL DE SAO BERNARDO
14CenterEspecialidadeCount Number of NamesCenterCount Number of Names362 St - SET (SJuliĆ£o, SMGraƧa)301 Lx - AMA (Venteira)GILBERTO MEDICINA GERAL E FAMILIARHOSPITAL DR FERNANDO FONSECA EPE (AMADORA / SINTRA)
15HOSPITAL DA LUZ LISBOAMEDICINA GERAL E FAMILIAR1HOSPITAL DA LUZ LISBOA2363 St - SET (SSebastiĆ£o, GPAGuerra, Sado)362 St - SET (SJuliĆ£o, SMGraƧa)VITOR MEDICINA GERAL E FAMILIARPOSTO CLINICO PSP / SETUBAL
16HOSPITAL DA LUZ LISBOACARDIOLOGIA1362 St - SET (SJuliĆ£o, SMGraƧa)JOSE MEDICINA GERAL E FAMILIARConsultorio DR. BOCAGE
17315 Lx - XPTO (SAntĆ³nio - Este)ANTONIOCARDIOLOGIACH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
18332 Lx - XPTO (SDBenfica - Norte)JOAOREUMATOLOGIAHOSPITAL DA LUZ TORRES DE LISBOA
19333 Lx - XPTO (SDBenfica - Norte)LEO MEDICINA GERAL E FAMILIARHOSPITAL DA LUZ TORRES DE LISBOA
20334 Lx - XPTO (SDBenfica - Norte)JANNEMEDICINA GERAL E FAMILIARHOSPITAL DA LUZ TORRES DE LISBOA
21315 Lx - XPTO (SAntĆ³nio - Este)ANABELA MED INTERNACH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
22361 St - SET (NSAnunciada, SLourenƧo, SSimĆ£o)ALBERTO MANUELMEDICINA GERAL E FAMILIARHOSPITAL DA LUZ CLINICA LUISA TODI
23357 St - ABC (Caparica, Trafaria)ANTONIO MANUELMEDICINA GERAL E FAMILIARUSF VISTA TEJO (CS COSTA CAPARICA)
24316 Lx - XPTO (SMMaior)MANUEL FERNANDO CARDIOLOGIAClĆ­nica SOCORROS MUTUOS EMPREGADOS COMERCIO INDUSTRIA
25333 Lx - XPTO (Carnide)LUIS FILIPECARDIOLOGIAHOSPITAL DA LUZ LISBOA
Sheet2
Cell Formulas
RangeFormula
H3:H15H3=SORT(UNIQUE(I3:I25))
B6:D7B6=LET( brick,I3:I27, center,L3:L27, especialidade,K3:K27,name,J3:J27,f,UNIQUE(FILTER(CHOOSE({2,3},brick,center,especialidade),(brick=B3))), ub,INDEX(f,0,1), uc,INDEX(f,0,2), ue,INDEX(f,0,3), somaespecialidade,COUNTIFS(center,ub,especialidade,uc),SORT(CHOOSE({1,2,3},ub,uc,somaespecialidade),1))
B15:F16B15=LET(brick,I3:I27,center,L3:L27,especialidade,K3:K27,br,B3,f,SORT(UNIQUE(FILTER(CHOOSE({1,2},center,especialidade),brick=br)),1),ub,INDEX(f,0,1),uc,INDEX(f,0,2),somaespecialidade,COUNTIFS(center,ub,especialidade,uc),s,SEQUENCE(ROWS(ub)),ctr,UNIQUE(ub),ctrno,COUNTIFS(brick,br,center,ctr),IFERROR(CHOOSE({1,2,3,4,5},ub,uc,somaespecialidade,INDEX(ctr,s),INDEX(ctrno,s)),""))
Dynamic array formulas.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

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
Back
Top