Dit moet toch eenvoudiger kunnen?

[Severijns]

In een document dat ik gebruik staat de onderstaande formule.
Deze werkt prima maar hij is wel erg lang.
Dit zou toch eenvoudiger moeten kunnen?
Iemand enig idee?

SOMPRODUCT((('Overzicht'!$AG$8:$AG$1007="V")+('Overzicht'!$AG$8:$AG$1007="T"))*(AANTALLEN.ALS('Overzicht'!$M$8:$M$1007;'Overzicht'!$M8:$M1007;'Overzicht'!$Z$8:$Z$1007;"Org";'Overzicht'!$AG$8:$AG$1007;"V")>0)*(AANTALLEN.ALS('Overzicht'!$M$8:$M$1007;'Overzicht'!$M8:$M1007;'Overzicht'!$Z$8:$Z$1007;"Org";'Overzicht'!$AG$8:$AG$1007;"T")>0))/2+SOMPRODUCT((('Overzicht'!$AG$8:$AG$1007="V")+('Overzicht'!$AG$8:$AG$1007="T"))*(AANTALLEN.ALS('Overzicht'!$M$8:$M$1007;'Overzicht'!$M8:$M1007;'Overzicht'!$Z$8:$Z$1007;"Tev";'Overzicht'!$AG$8:$AG$1007;"V")>0)*(AANTALLEN.ALS('Overzicht'!$M$8:$M$1007;'Overzicht'!$M8:$M1007;'Overzicht'!$Z$8:$Z$1007;"Tev";'Overzicht'!$AG$8:$AG$1007;"T")>0))/2

Bij voorbaat dank voor de moeite.

Rob Severijns

 

[Excelbat]

Plaats even een voorbeeldbestand s.v.p.

Greetz/Excelbat

 

[Severijns]

Excelbat,

Het originele bestand kan ik daarvoor niet gebruiken.
Dat zit vol met allerlei andere formules, VBA en werkbladen.
Daarnaast zit het vol met persoonsgegevens.
Zal een nieuw document moeten maken zonder andere formules en VBA.

Rob

 

[Severijns]

Bij deze het voorbeeldbestand met daarin de (iets aangepaste) formule.
De oplossing van de formule zou 7 moeten zijn.

Rob

 

[VenA]

Wat wil je bereiken dan? 't is een nogal lange matrixformule over een groot bereik waardoor evalueren een beetje lastig is. Het voorbeeldbestand is ook weinig zeggend? 7 als uitkomst en dat heb je dus niets meer aan doen.:d

 

[Severijns]

Beste VenA,

Bedankt voor je reactie.
Zoals je zelf al in jouw commentaar aangeeft is het nogal een lange formule.
Dat gaf ik in mijn eerste bericht ook al aan.
Mijn vraag was dan ook of deze korter en dus eenvoudiger kan.
Dat maakt hem hopelijk beter leesbaar.

Uiteindelijk gaat het natuurlijk maar om een drietal kolommen waarin wordt geteld en gekeken wordt naar specifieke waarden waaraan moet worden voldaan.

In het nieuwe voorbeeldbestand zijn alle overbodige kolommen verwijderd en is aan de kolom een Naam (met bereik) toegekend.

=SOMPRODUCT(((Kolom_3="V")+(Kolom_3="T"))*(AANTALLEN.ALS(Kolom_1;Kolom_1;Kolom_2;"Org";Kolom_3;"V")>0)*(AANTALLEN.ALS(Kolom_1;Kolom_1;Kolom_2;"Org";Kolom_3;"T")>0))/2+SOMPRODUCT(((Kolom_3="V")+(Kolom_3="T"))*(AANTALLEN.ALS(Kolom_1;Kolom_1;Kolom_2;"Tev";Kolom_3;"V")>0)*(AANTALLEN.ALS(Kolom_1;Kolom_1;Kolom_2;"Tev";Kolom_3;"T")>0))/2

Dat oogt in elk geval een stuk eenvoudiger en de formule is minder lang.
De vraag of het nog eenvoudiger kan blijft staan maar wellicht moet ik genoegen nemen met de huidige oplossing omdat het de een werkende formule is.

Rob

 

[lam201]

Uiteindelijk gaat het natuurlijk maar om een drietal kolommen waarin wordt geteld en gekeken wordt naar specifieke waarden waaraan moet worden voldaan.

Wat wordt er geteld?
Aan welke waarden moet voldaan worden?
Hoe kom je op 7?

't is een beetje onduidelijk voor mij nog..

 

[jkpieterse]

Als je nou eens de moeite neemt om ons uit te leggen wat de formule geacht wordt te berekenen (behalve dat het antwoord 7 is)?

 

[Severijns]

Lam201 & jkpieterse,

Excuus maar ik was in de veronderstelling dat uit de formule al blijkt wat er op basis van welke criteria berekend moet worden. Kennelijk is dit niet zo.
Feitelijk bestaat de totale formule uit twee nagenoeg identieke berekeningen.

=SOMPRODUCT(((Kolom_3="V")+(Kolom_3="T"))*(AANTALLEN.ALS(Kolom_1;Kolom_1;Kolom_2;[COLOR="#FF0000"]"Org"[/COLOR];Kolom_3;"V")>0)*(AANTALLEN.ALS(Kolom_1;Kolom_1;Kolom_2;[COLOR="#FF0000"]"Org"[/COLOR];Kolom_3;"T")>0))/2

=SOMPRODUCT(((Kolom_3="V")+(Kolom_3="T"))*(AANTALLEN.ALS(Kolom_1;Kolom_1;Kolom_2;[COLOR="#FF0000"]"Tev"[/COLOR];Kolom_3;"V")>0)*(AANTALLEN.ALS(Kolom_1;Kolom_1;Kolom_2;[COLOR="#FF0000"]"Tev"[/COLOR];Kolom_3;"T")>0))/2

Omdat, naar ik begreep, binnen de formule AANTALLEN.ALS niet naar twee verschillende criteria ("Org" en "Tev") binnen hetzelfde bereik gekeken kan worden heb ik achter het eerste deel, met criterium "Org" een zelfde formule geplaatst met als criterium "Tev".

Ik had gehoopt dat iemand mij kon vertellen hoe ik het criterium "Tev" toch binnen het eerste deel van de formule zou kunnen implementeren waardoor het 2e deel niet meer benodigd zou zijn.
Dat het antwoord 7 zou moeten zijn is eigenlijk alleen maar aangegeven ter controle of de uitkomst van de nieuwe formule juist is ten opzichte van de ouden formule.
Hopelijk maakt dit het e.e.a. iets meer duidelijk.
Zo niet dan zou ik zeggen "niet al te veel moeite in steken" zoals de formule nu is werkt hij.

Rob

 

[HSV]

Waaruit moet blijken dat het getal 7 juist is?

 

[AlexCEL]

WAT de formule doet is wel duidelijk, het gaat om het WAAROM.... misschien is er een andere slimme oplossing te bedenken. Persoonlijk vind ik dat wel een leuke uitdaging.

 

[Severijns]

Uit het feit dat in zowel het originele document als in dit bijgevoegde document na handmatige filtering blijkt dat het antwoord 7 is.

Voor het gemak heb ik de codes die in Kolom_1 staan en die in Kolom_2 voorkomen als "Org" en in Kolom_3 zowel een code "T" als "V" hebben rood gekleurd.
In het bijgevoegde document is de gefilterde lijst te zien.

Rob

 

[AlexCEL]

Dus je wilt weten hoeveel keer voorkomt dat een nummer in kolom 1 zowel V als T in kolom 3 i.c.m Org (en Tev) in kolom 2 heeft?

 

[HSV]

Je filtert op rode tekst.
waarom doet B8: D8 niet mee?

=Org en V

Het getal moet twee keer voorkomen?

 

[Severijns]

AlexCEL,

Dat klopt.
De code "Tev" komt in het voorbeeldbestand nu wel is waar niet voor maar is wel een criterium waarmee gerekend moet worden.

Uiteindelijk ben ik middels het combineren van SOMPRODUCT i.c.m. AANTALLEN.ALS tot mijn oplossing gekomen.
Door was ik al heel blij mee.
Ik had echter wel het gevoel dat het eenvoudiger moest kunnen. Vandaar mijn vraag.

Rob

 

[Severijns]

HSV,

Omdat de code uit B8 slechts voorkomt met de in kolom_3 de code "V" en niet nogmaals op een andere regel met de code "T".
Beide met in kolom_2 de code "Org"

Rob

 

[Severijns]

Nog even ter aanvulling.

Het gaat uiteindelijk om het aantal unieke codes uit Kolom_1.
Vandaar de /2 aan het einde van ieder formule deel.
Wat dus eigenlijk 14/2=7 is.

Rob

 

[sylvester-ponte]

deze voor bestand uit post 12:

=SOMPRODUCT((B8:B843&C8:C843=B9:B844&C9:C844)*(C8:C843="Org")*((D8:D843="V")+(D8:D843="T"))*((D9:D844="V")+(D9:D844="T")))

 

[Severijns]

Sylvester-Ponte,

Dank voor jouw oplossing.
De formule is precies datgene waar ik naar op zoek was. :thumb:
Beduidend korter en eenvoudiger te lezen.
Ik heb hem wel ietsjes aangepast omdat het criterium "Tev" niet was meegenomen.
Dit is hem uiteindelijk geworden:

=SOMPRODUCT((B8:B843&C8:C843=B9:B844&C9:C844)*((C8:C843="Org")+(C8:C843="Tev"))*((D8:D843="V")+(D8:D843="T"))*((D9:D844="V")+(D9:D844="T")))

Alleen nog even het bereik aanpassen voor gebruik in het originele bestand.

Alle overigen wil ik eveneens bedanken voor het meedenken en de kritische notenen/vragen.


Rob

 

[sylvester-ponte]

met vba is het natuurlijk nog eenvoudiger.
als je interesse hebt laat het dan weten.