Optellen als een cel een bepaalde waarde bevat...

[dirkdrent]

Ik heb gezocht maar begrijp niets van de andere oplossingen.
In mijn excellijst heb ik 2 tabbladen gemaakt op het eerste tabblad een kolom genaamd gebied(A1) en een kolom genaamd klant(b1) en een kolom omzet klant(c1).

Nu heb ik in deze lijst verschillende gebieden (de gebieden worden weergegeven met cijfers). Nu wil ik graag uit de lijst op het tweede tabblad de totalen berekenen.
Graag wil ik per gebied kan berekenen hoeveel klanten er per gebied zijn en wat de totale omzet per gebied is. Nu kan ik wel alles gaan selecteren en verwijzen naar blad 1. Maar wanneer er een klant in een bepaald gebied bij komt moet ik alle formules weer aanpassen.Wanneer ik bijvoorbeeld het aantal klanten wil weten uit het gebied 1 dacht ik zelf aan een bepaalde voorwaarde zoiets als bijvoorbeeld wanneer er in kolom A de waarde 1 voorkomt dan alles optellen wat in B2 in deze rij hoort. Ik weet niet hoe ik het moet vertalen in formules. Hoop dat het zo duidelijk is.

In de bijlage heb ik een voorbeeldje gemaakt.

 

[Wigi]

Zie bijlage Dirk.

Wigi

 

[dirkdrent]

Voor zover klopt ie...

 

[dirkdrent]

De totaalomzet om deze te berekenen is nu duidelijk. Maar hoe kan ik zien hoe vaak een product verkocht is of wel het aantal keren dat een bepaald product voorkomt in een gebied?

 

[bandito bob]

De totaalomzet om deze te berekenen is nu duidelijk. Maar hoe kan ik zien hoe vaak een product verkocht is of wel het aantal keren dat een bepaald product voorkomt in een gebied?

Dat kan met de functie somproduct.

Voor cel E2 de volgende formule:

=SOMPRODUCT((specifiek!$A$2:$A$23=Totaal!$A2)*(specifiek!$D$2:$D$23=Totaal!E$1))

Vervolgens kan je deze doortreken.

 

[dirkdrent]

Kan ik dit ook doen met meerdere waarden? Ik heb bijvoorbeeld nog een kolom klassificatie elke klant krijgt een klassificatie. De klassificatie loopt van A1 tot A3, B1 tot B3 en C1 tot C3. Is het nu ook mogelijk dat ik in een gebied kan berekenen hoeveel A klanten er zich in betreffend gebied bevinden?

Of moet ik ze eerst stuk voor stuk per gebied gaan berekenen en dan vervolgens de som per gebied van A1, A2 en A3 bij elkaar op te tellen?

 

[bandito bob]

Kan ik dit ook doen met meerdere waarden? Ik heb bijvoorbeeld nog een kolom klassificatie elke klant krijgt een klassificatie. De klassificatie loopt van A1 tot A3, B1 tot B3 en C1 tot C3. Is het nu ook mogelijk dat ik in een gebied kan berekenen hoeveel A klanten er zich in betreffend gebied bevinden?

Of moet ik ze eerst stuk voor stuk per gebied gaan berekenen en dan vervolgens de som per gebied van A1, A2 en A3 bij elkaar op te tellen?

Mij is niet geheel duidelijk wat je precies wilt, maar als die classificatie ook in die database staat dan moet dat geen probleem zijn.

 

[dirkdrent]

In het voorbeeld staat wordt het duidelijk. Ik wil graag het totaal aantal A-klanten zien, B-klanten en C-klanten per categorie. De categorie van de A-klanten bestaat uit de klanten die een klassificatie hebben of A1 of A2 of A3. Dit zelfde geldt ook voor de B-klanten en de C-klanten.

 

[bandito bob]

In het voorbeeld staat wordt het duidelijk. Ik wil graag het totaal aantal A-klanten zien, B-klanten en C-klanten per categorie. De categorie van de A-klanten bestaat uit de klanten die een klassificatie hebben of A1 of A2 of A3. Dit zelfde geldt ook voor de B-klanten en de C-klanten.

Iets lastiger, daar je niet de gehele cellen kan vergelijken, maar je moet gedeeltes van cellen vergelijken (b.v. alleen de A van A1). Hiervoor heb ik de functie LINKS en DEEL gebruikt.
Deze formule voor H2:

=SOMPRODUCT((specifiek!$A$2:$A$23=Totaal!$A2)*(LINKS(specifiek!$D$2:$D$23;1)=DEEL(Totaal!H$1;5;1)))

deze kan je vervolgens doortrekken.

 

[dirkdrent]

Dit is echt briljant... thanx

Weet je toevallig ook nog hoe ik het aantal verschillende adressen(locaties) kan tellen dan is het helemaal fantastisch?

 

[nobody11]

Dit is echt briljant... thanx

Weet je toevallig ook nog hoe ik het aantal verschillende adressen(locaties) kan tellen dan is het helemaal fantastisch?

dit is ongetwijfeld wat "moeilijker" gedaan dan het wss kan; maar ik leer graag bij dus hoor het wel als het sneller kan..
dit is mijn oplossing (met een gewoon voorbeeld bestandje)

 

[bandito bob]

Als je dat zonder omweg (zoals nobody11) wilt doen dan zal je een matrix formule moeten gebruiken. Deze voldoet voor cel D2:

=SOM(ALS(specifiek!A$2:A$23=Totaal!A2;1/AANTAL.ALS(specifiek!C$2:C$23;specifiek!C$2:C$23);0))

Let op!! matrix formule, deze moet je bevestigen met CTRL-SHIFT-ENTER ipv ENTER.

vervolgens weer doortrekken naar beneden.

p.s. de "gewone" somproduct formule zou ik als ik jou was eens goed bekijken. Waarschijnlijk snap je dan de werking. Dan kan je hem later misschien zelf gebruiken.:thumb:

 

[nobody11]

Als je dat zonder omweg (zoals nobody11) wilt doen dan zal je een matrix formule moeten gebruiken. Deze voldoet voor cel D2:

=SOM(ALS(specifiek!A$2:A$23=Totaal!A2;1/AANTAL.ALS(specifiek!C$2:C$23;specifiek!C$2:C$23);0))

Let op!! matrix formule, deze moet je bevestigen met CTRL-SHIFT-ENTER ipv ENTER.

vervolgens weer doortrekken naar beneden.

p.s. de "gewone" somproduct formule zou ik als ik jou was eens goed bekijken. Waarschijnlijk snap je dan de werking. Dan kan je hem later misschien zelf gebruiken.:thumb:

raar, als ik in mjn file de somproduct formule doe:
=SOMPRODUCT(D1219;C12:C19) dan krjig ik als oplossing 0 ipv 5.. wat doe ik fout? moet ik die waar en onwaar omzetten nr 1 en 0? is dat niet iets met -- in de formule?

EDIT: gevonden

=SOMPRODUCT(D1219;--Ophalen)

 

[Wigi]

Regret to say Paul, maar dit klopt niet.

Stel dat het adres uit cel C2 ook voorkomt in cel C23. Dan gaat het fout doordat de AANTAL.ALS wordt toegepast op het hele bereik.

Wigi

 

[dirkdrent]

Iets lastiger, daar je niet de gehele cellen kan vergelijken, maar je moet gedeeltes van cellen vergelijken (b.v. alleen de A van A1). Hiervoor heb ik de functie LINKS en DEEL gebruikt.
Deze formule voor H2:

=SOMPRODUCT((specifiek!$A$2:$A$23=Totaal!$A2)*(LINKS(specifiek!$D$2:$D$23;1)=DEEL(Totaal!H$1;5;1)))

deze kan je vervolgens doortrekken.

=SOMPRODUCT((specifiek!$A$2:$A$23=Totaal!$A2)*(LINKS(specifiek!$D$2:$D$23;1)=DEEL(Totaal!H$1;5;1)))

Deze begrijp ik volgens mij gedeeltelijk:
=somproduct = is de som van de waarde van je product(gegeven) dat je opgeeft
(specifiek!$A$2:$A$23=Totaal!$A2) = specifiek! is de verwijzing van het tabblad, $A$2:$A$23=totaal!$A2 = waarde in de reeks cellen A2 en A23 moet voldaan aan de waarde van A2 op het tabblad totaal.

Maar waar staat * voor???
en waar staat LINKS voor????
en ;1 ????

 

[bandito bob]

Regret to say Paul, maar dit klopt niet.

Stel dat het adres uit cel C2 ook voorkomt in cel C23. Dan gaat het fout doordat de AANTAL.ALS wordt toegepast op het hele bereik.

Wigi

Je hebt zoals zo vaak gelijk.....hmmm ik zal eens kijken hoe de formule dan moet
Of heb jij de oplossing al....

Toch grappig hoe een topic van een simpele som.als en aantal.als gaat via somproduct naar een lastige matrix formule

 

[bandito bob]

=SOMPRODUCT((specifiek!$A$2:$A$23=Totaal!$A2)*(LINKS(specifiek!$D$2:$D$23;1)=DEEL(Totaal!H$1;5;1)))

Deze begrijp ik volgens mij gedeeltelijk:
=somproduct = is de som van de waarde van je product(gegeven) dat je opgeeft
(specifiek!$A$2:$A$23=Totaal!$A2) = specifiek! is de verwijzing van het tabblad, $A$2:$A$23=totaal!$A2 = waarde in de reeks cellen A2 en A23 moet voldaan aan de waarde van A2 op het tabblad totaal.

Maar waar staat * voor???
en waar staat LINKS voor????
en ;1 ????

Je pakt ook gelijk de lastigste he
Begrijp je de simpelere somproduct formules al?

p.s. kijk eens in de excel help (F1) naar de functies LINKS en DEEL.

 

[Wigi]

en waar staat LINKS voor????

LINKS staat voor de meest RECHTSE karakters in een cel...



voor de meest LINKSE karakters waarschijnlijk

Wil je zoiets onder de knie krijgen: Googlen, zoekfunctie van het forum gebruiken (voor SOMPRODUCT niet in de helpfunctie gaan kijken want staat niet veel van in).
Heb ik ook moeten doen en het is toch goed gekomen dus...

 

[Wigi]

Je hebt zoals zo vaak gelijk.....hmmm ik zal eens kijken hoe de formule dan moet
Of heb jij de oplossing al....

Toch grappig hoe een topic van een simpele som.als en aantal.als gaat via somproduct naar een lastige matrix formule

Als het over verschillende gebieden gaat in kolom A en de adressen komen buiten een gebied niet voor, dan hoef je de formule niet aan te passen.

 

[bandito bob]

Als het over verschillende gebieden gaat in kolom A en de adressen komen buiten een gebied niet voor, dan hoef je de formule niet aan te passen.

Precies, het zal denk ik niet voorkomen dat een klant uit een ander gebied voorkomt op hetzelfde adres:thumb: