Excel aantal van celbereik waarbij ander cel niet dubbel mag voorkomen.

[BJMarc]

Voor de wizards hier waarschijnlijk easy, maar ik kom er ff niet uit.

Ik heb een tabel waarbij ik uit "zeg maar" 2 rijen aantallen wil tellen. Als de waarde uit tabelrij 2 al eerder is geteld (komt al voor), mag het aantal niet extra worden geteld. Een beetje warrig voorbeeld, zal het proberen duidelijk te maken.

Waarde	Naam
Appel	Groenteboer
Appel	Groenteboer
Appel	Supermarkt
Peer	Groenteboer

Appel is nu 3 keer totaal als waarde te vinden, maar 2 keer hoort hier de naam "Groenteboer" bij, deze naam mag niet dubbel geteld worden bij de waardes. De uitkomst zou dus 2 moeten zijn, omdat er slechts 2 unieke waardes van de combinatie Appel & Groenteboer bestaan.

De som of het aantal van de uitkomst dien ik in een aparte cel te plaatsen, om hier vervolgens verdere berekeningen mee te doen.

Wie oh wie kan en wil me helpen, ik ben me waarschijnlijk al blind aan het staren geweest en ben echt geen wizard.

 

[AlexCEL]

Deze voor C6?

=AANTALLEN.ALS(A$6:A$9;A6;B$6:B$9;B6)

 

[BJMarc]

Misschien mis ik iets, maar waar komt het bereik 6 t/m 9 vandaan? Of was dat als voorbeeld?

 

[AlexCEL]

Ja, ik had de tabel gekopieerd naar mijn Excel, en daar staat deze in A6 t/m B9. Mag je naar wens aanpassen, gaat om de formule.

Je ziet: het is beter een EXCEL voorbeeldje bij te plaatsen, om dit soort verwarring te voorkomen...

 

[BJMarc]

Top! Begrijp het deels. Maar nu is de complete lijst natuurlijk veel groter en staat en kan ook niet zo mooi op volgorde vanwege andere zaken.

De te toetsen naam kan in B in verschillende cellen voorkomen (bereik)

 

[AlexCEL]

Geen probleem toch? Formule telt aantal keer dat de combinatie voorkomt.

 

[BJMarc]

Ja, zal even uitweiden, nu ik ook je voorbeeld heb bekeken.

De uitkomst van de criteria komt op een ander tabblad en de complete selectie is in een bereik van kolom A en kolom B.
Dit komt in mijn optiek uit op iets als :

=AANTALLEN.ALS(Groente!$A$1:Groente!$A$5000;A3;Winkel!$B$1:Winkel!$B$5000;Winkel!$B$1:Winkel!$B$5000)

Het probleem zit in mijn idee in de laatste criteria.
De groente is bekend uit A3, Maar de winkel kan overal in een bereik voorkomen en kan ik niet individueel benoemen. Er zou dus iets moeten zijn als =UNIEK(celbereik). Als ik dat zo een beetje goed uitleg.

 

[AlexCEL]

Maak eens een EXCEL voorbeeldje.

En gebruik je Excel 365?

 

[BJMarc]

2019

Zal even werken aan een voorbeeldje.

 

[BJMarc]

Ik hoop dat dit wat duidelijker is. Formule klopt uiteraard nog niet.

 

[BJMarc]

Ik zou het anders neerzetten als iets in de trend van :

Als A+B =>1 Then C =1

De combinatie mag er dus wel zijn, maar mag slecht één keer tellen. Elke keer dat A + B = Een eerdere A + B telt deze dus niet meer mee.

 

[AlexCEL]

Geeft deze de gewenste resultaten? (voor Formule!E3)

=SOMPRODUCT(ALS(Gegevens!A$2:A$15=$D3;1/AANTALLEN.ALS(Gegevens!A$2:A$15;Gegevens!A$2:A$15;Gegevens!$B$2:$B$15;Gegevens!$B$2:$B$15);0))

En ter info voor de Excel 365 gebruikers, deze doet hetzelfde:

=AANTALARG(UNIEK(FILTER(Gegevens!B$2:B$15;Gegevens!A$2:A$15=D3)))

 

[BJMarc]

We zijn er bijna, denk ik. Kan de uitkomst momenteel niet helemaal verklaren.

Waarde	Aantal
Appel	0,5
Peer	0,142857

 

[AlexCEL]

Probeer eens de formule af te sluiten met Control+Shift+Enter, het is een matrixfunctie. In Excel 365 hoeft niet meer, maar in eerdere versies van Excel nog wel.

 

[BJMarc]

Top!!!

Dat was hem inderdaad! Wat een geweldige service! Heel erg bedankt voor dit!