Let formula?

[Lacan]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		Brick							Brick	Name	Especialidade	Center
3		363 St - SET (SSebastião, GPAGuerra, Sado)						301 Lx - AMA (Venteira)	354 St - ABC (CPiedade)	DELFINA	MEDICINA GERAL E FAMILIAR	Hospital PARTICULAR DE ALMADA
4								308 Lx - XPTO (Benfica - Sul)	333 Lx - XPTO (Carnide)	MARIA	MEDICINA GERAL E FAMILIAR	HOSPITAL DA LUZ LISBOA
5		Center	Especialidade	Count Number of Names				315 Lx - XPTO (SAntónio - Este)	363 St - SET (SSebastião, GPAGuerra, Sado)	LEONOR	MEDICINA GERAL E FAMILIAR	USF BAIA AZUL (ULS ARRABIDA EPE)
6		CH SETUBAL EPE - HOSPITAL DE SAO BERNARDO	MEDICINA GERAL E FAMILIAR	#REF!				316 Lx - XPTO (SMMaior)	363 St - SET (SSebastião, GPAGuerra, Sado)	ANNA	CARDIOLOGIA	USF BAIA AZUL (ULS ARRABIDA EPE)
7		USF BAIA AZUL (ULS ARRABIDA EPE)	MEDICINA GERAL E FAMILIAR	#REF!				332 Lx - XPTO (SDBenfica - Norte)	363 St - SET (SSebastião, GPAGuerra, Sado)	JOHN	CARDIOLOGIA	USF BAIA AZUL (ULS ARRABIDA EPE)
8		USF BAIA AZUL (ULS ARRABIDA EPE)	CARDIOLOGIA	#REF!				333 Lx - XPTO (Carnide)	363 St - SET (SSebastião, GPAGuerra, Sado)	DENISE	GASTRO	USF BAIA AZUL (ULS ARRABIDA EPE)
9		USF BAIA AZUL (ULS ARRABIDA EPE)	GASTRO	#REF!				333 Lx - XPTO (SDBenfica - Norte)	363 St - SET (SSebastião, GPAGuerra, Sado)	PAUL	NEURO	USF BAIA AZUL (ULS ARRABIDA EPE)
10		USF BAIA AZUL (ULS ARRABIDA EPE)	NEURO	#REF!				334 Lx - XPTO (SDBenfica - Norte)	308 Lx - XPTO (Benfica - Sul)	MARIA LUISA	MEDICINA GERAL E FAMILIAR	USF GERACOES (UCSP BENFICA)
11								354 St - ABC (CPiedade)	362 St - SET (SJulião, SMGraça)	MANUEL	MEDICINA GERAL E FAMILIAR	SAMS - POSTO CLINICO REGIONAL DE SETUBAL
12								357 St - ABC (Caparica, Trafaria)	315 Lx - XPTO (SAntónio - Este)	AUGUSTO	MEDICINA INTERNA	CH LISBOA CENTRAL EPE - HOSPITAL STO ANTONIO DOS CAPUCHOS
13								361 St - SET (NSAnunciada, SLourenço, SSimão)	363 St - SET (SSebastião, GPAGuerra, Sado)	ISABEL	MEDICINA GERAL E FAMILIAR	CH SETUBAL EPE - HOSPITAL DE SAO BERNARDO
14								362 St - SET (SJulião, SMGraça)	301 Lx - AMA (Venteira)	GILBERTO	MEDICINA GERAL E FAMILIAR	HOSPITAL DR FERNANDO FONSECA EPE (AMADORA / SINTRA)
15								363 St - SET (SSebastião, GPAGuerra, Sado)	362 St - SET (SJulião, SMGraça)	VITOR	MEDICINA GERAL E FAMILIAR	POSTO CLINICO PSP / SETUBAL
16									362 St - SET (SJulião, SMGraça)	JOSE	MEDICINA GERAL E FAMILIAR	Consultorio DR. BOCAGE
17									315 Lx - XPTO (SAntónio - Este)	ANTONIO	CARDIOLOGIA	CH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
18									332 Lx - XPTO (SDBenfica - Norte)	JOAO	REUMATOLOGIA	HOSPITAL DA LUZ TORRES DE LISBOA
19									333 Lx - XPTO (SDBenfica - Norte)	LEO	MEDICINA GERAL E FAMILIAR	HOSPITAL DA LUZ TORRES DE LISBOA
20									334 Lx - XPTO (SDBenfica - Norte)	JANNE	MEDICINA GERAL E FAMILIAR	HOSPITAL DA LUZ TORRES DE LISBOA
21									315 Lx - XPTO (SAntónio - Este)	ANABELA	CARDIOLOGIA	CH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
22									361 St - SET (NSAnunciada, SLourenço, SSimão)	ALBERTO MANUEL	MEDICINA GERAL E FAMILIAR	HOSPITAL DA LUZ CLINICA LUISA TODI
23									357 St - ABC (Caparica, Trafaria)	ANTONIO MANUEL	MEDICINA GERAL E FAMILIAR	USF VISTA TEJO (CS COSTA CAPARICA)
24									316 Lx - XPTO (SMMaior)	MANUEL FERNANDO	CARDIOLOGIA	Clínica SOCORROS MUTUOS EMPREGADOS COMERCIO INDUSTRIA
25									333 Lx - XPTO (Carnide)	LUIS FILIPE	CARDIOLOGIA	HOSPITAL DA LUZ LISBOA
26
27
28
29
30
31
32
Folha1

Cell Formulas
Range	Formula
H3:H15	H3	=SORT(UNIQUE(I3:I25))
B6:D10	B6	=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
Cell	Allow	Criteria
B3	List	=$H$3#

 

[Lacan]

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
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Brick							Brick	Name	Especialidade	Center
3		333 Lx - XPTO (Carnide)						301 Lx - AMA (Venteira)	354 St - ABC (CPiedade)	DELFINA	MEDICINA GERAL E FAMILIAR	Hospital PARTICULAR DE ALMADA
4								308 Lx - XPTO (Benfica - Sul)	333 Lx - XPTO (Carnide)	MARIA	MEDICINA GERAL E FAMILIAR	HOSPITAL DA LUZ LISBOA
5		Center	Especialidade	Count Number of Names	Center	Count Number of Names		315 Lx - XPTO (SAntónio - Este)	363 St - SET (SSebastião, GPAGuerra, Sado)	LEONOR	MEDICINA GERAL E FAMILIAR	USF BAIA AZUL (ULS ARRABIDA EPE)
6		HOSPITAL DA LUZ LISBOA	MEDICINA GERAL E FAMILIAR	1				316 Lx - XPTO (SMMaior)	363 St - SET (SSebastião, GPAGuerra, Sado)	ANNA	CARDIOLOGIA	USF BAIA AZUL (ULS ARRABIDA EPE)
7		HOSPITAL DA LUZ LISBOA	CARDIOLOGIA	1				332 Lx - XPTO (SDBenfica - Norte)	363 St - SET (SSebastião, GPAGuerra, Sado)	JOHN	CARDIOLOGIA	USF BAIA AZUL (ULS ARRABIDA EPE)
8								333 Lx - XPTO (Carnide)	363 St - SET (SSebastião, GPAGuerra, Sado)	DENISE	GASTRO	USF BAIA AZUL (ULS ARRABIDA EPE)
9								333 Lx - XPTO (SDBenfica - Norte)	363 St - SET (SSebastião, GPAGuerra, Sado)	PAUL	CARDIOLOGIA	USF BAIA AZUL (ULS ARRABIDA EPE)
10								334 Lx - XPTO (SDBenfica - Norte)	308 Lx - XPTO (Benfica - Sul)	MARIA LUISA	MEDICINA GERAL E FAMILIAR	USF GERACOES (UCSP BENFICA)
11								354 St - ABC (CPiedade)	362 St - SET (SJulião, SMGraça)	MANUEL	MEDICINA GERAL E FAMILIAR	SAMS - POSTO CLINICO REGIONAL DE SETUBAL
12								357 St - ABC (Caparica, Trafaria)	315 Lx - XPTO (SAntónio - Este)	AUGUSTO	CARDIOLOGIA	CH LISBOA CENTRAL EPE - HOSPITAL STO ANTONIO DOS CAPUCHOS
13								361 St - SET (NSAnunciada, SLourenço, SSimão)	363 St - SET (SSebastião, GPAGuerra, Sado)	ISABEL	MEDICINA GERAL E FAMILIAR	CH SETUBAL EPE - HOSPITAL DE SAO BERNARDO
14		Center	Especialidade	Count Number of Names	Center	Count Number of Names		362 St - SET (SJulião, SMGraça)	301 Lx - AMA (Venteira)	GILBERTO	MEDICINA GERAL E FAMILIAR	HOSPITAL DR FERNANDO FONSECA EPE (AMADORA / SINTRA)
15		HOSPITAL DA LUZ LISBOA	MEDICINA GERAL E FAMILIAR	1	HOSPITAL DA LUZ LISBOA	2		363 St - SET (SSebastião, GPAGuerra, Sado)	362 St - SET (SJulião, SMGraça)	VITOR	MEDICINA GERAL E FAMILIAR	POSTO CLINICO PSP / SETUBAL
16		HOSPITAL DA LUZ LISBOA	CARDIOLOGIA	1					362 St - SET (SJulião, SMGraça)	JOSE	MEDICINA GERAL E FAMILIAR	Consultorio DR. BOCAGE
17									315 Lx - XPTO (SAntónio - Este)	ANTONIO	CARDIOLOGIA	CH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
18									332 Lx - XPTO (SDBenfica - Norte)	JOAO	REUMATOLOGIA	HOSPITAL DA LUZ TORRES DE LISBOA
19									333 Lx - XPTO (SDBenfica - Norte)	LEO	MEDICINA GERAL E FAMILIAR	HOSPITAL DA LUZ TORRES DE LISBOA
20									334 Lx - XPTO (SDBenfica - Norte)	JANNE	MEDICINA GERAL E FAMILIAR	HOSPITAL DA LUZ TORRES DE LISBOA
21									315 Lx - XPTO (SAntónio - Este)	ANABELA	MED INTERNA	CH LISBOA CENTRAL EPE - HOSPITAL SANTA MARTA
22									361 St - SET (NSAnunciada, SLourenço, SSimão)	ALBERTO MANUEL	MEDICINA GERAL E FAMILIAR	HOSPITAL DA LUZ CLINICA LUISA TODI
23									357 St - ABC (Caparica, Trafaria)	ANTONIO MANUEL	MEDICINA GERAL E FAMILIAR	USF VISTA TEJO (CS COSTA CAPARICA)
24									316 Lx - XPTO (SMMaior)	MANUEL FERNANDO	CARDIOLOGIA	Clínica SOCORROS MUTUOS EMPREGADOS COMERCIO INDUSTRIA
25									333 Lx - XPTO (Carnide)	LUIS FILIPE	CARDIOLOGIA	HOSPITAL DA LUZ LISBOA
Sheet2

Cell Formulas
Range	Formula
H3:H15	H3	=SORT(UNIQUE(I3:I25))
B6:D7	B6	=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:F16	B15	=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.

Now works fine @Eric W !!!
Sincerely just love your "Let" formulas. So simple, smooth and efficient!!
Everytime a formula as presented in is Forum before applied it have to change it through an excel translator website .
It seems to me you and other members use a different method? Can you please let know?
Thanks again for everything @Eric W

 

[Eric W]

I don't have any special tricks to translate a formula. I suggest the translator website if needed, and I know about the delimiters changing by region. Between those, I can usually figure out how to get a formula to work in different languages/regions.

Anyway, I'm glad it works for you!