[EXCEL] SOMMEN.ALS crit. aan meerdere criteria laten voldoen

[Gitarist]

Voor een omzetrapportage waar ik verantwoordelijk voor ben, ben ik op zoek naar een Excelformule die me kan helpen om omzet op een bepaalde manier inzichtelijk te maken. Ik hoop dat iemand me op het juiste spoor kan zetten.

Ik heb als bronbestand een lijst met omzet per klant. In dezelfde rij staat ook o.a. de accountmanager, het jaar en de bedrijfsafdeling van ons bedrijf waarbinnen de omzet gemaakt is.

Sterk vereenvoudigd zijn dit de waarden:
[bedrijfsafdelingA] [klantnaam1] [accountmanagerX] [omzetmaand1][omzetmaand2] [jaartalT]
[bedrijfsafdelingB] [klantnaam2] [accountmanagerY] [omzetmaand1][omzetmaand2] [jaartalT]
etc..
De accountmanagers kunnen bij elk bedrijfsonderdeel omzet behalen, in elke omzetmaand en in elk jaartal.

Ik wil graag een handig rapportje maken die de omzet van een selectie van accountmanagers bij elkaar optelt, maar er zijn ook accountmanagers die niet meegenomen moeten worden hierin. De bedrijfsafdeling, klantnaam en omzetmaand zijn in dat overzicht niet van belang.

Ik kan natuurlijk SOMMEN.ALS gebruiken, waarin ik als voorwaarde gebruik [bedrijfsafdeling];[jaartal];[accountmanagerX]+SOMMEN.ALS en dan hetzelfde riedeltje met [accountmanagerY] + nog een met Z, etc. Dat werkt gewoon, maar dan is de formule enorm lang aangezien wij nogal wat accountmanagers hebben rondlopen. Gevoelsmatig zeg ik dat dat met de mogelijkheden van Excel makkelijker moet kunnen.

---
Als "aanvliegroute" hiervoor heb ik bedacht om het lijstje met accountmanagers op een ander tabblad te zetten en dan de SOMMEN.ALS formule hiernaar te laten verwijzen, alleen krijg ik dit vooralsnog niet werkend.
De reden dat ik voor het lijstje accountmanagers gebruik wil maken van een ander tabblad is dat gedurende een jaar het regelmatig voorkomt dat er nieuwe accountmangers bijkomen en oude weggaan. Daarom zou het makkelijk zijn als ik de verwijzing naar het lijstje accountmanagers gewoon in tact kan laten en het lijstje met accountmanagers an sich dynamisch is. Maar ik weet niet of dat ook echt wel zo handig is...

Dus, (even de voorwaarden m.b.t. [jaartal], [bedrijfsafdeling], [klantnaam] en [omzetmaand] achterwege latend) zou de formule in mijn optiek worden:
SOMMEN.ALS(optelbereik;[XXbereik in tabblad met lijstje accountmanagers];[kolom met accountmanagers in omzetblad]

Bij de XX zou dan een formule moeten komen te staan waarmee Excel het hele lijstje accountmanagers, zoals gespecificeerd in een ander tabblad meeneemt als voorwaarde voor de formule.

Ik hoop dus dat het mogelijk is om binnen SOMMEN.ALS het criterium dat als voorwaarde de [accountmanager] bevat, kan laten kijken naar een hele groep van accountmanagers, die gespecificeerd zijn in het andere tabblad.

Misschien zit ik met mijn gedachtegang helemaal fout, maar dat hoop ik dan graag van jullie te horen

Ik hoop dat ik het enigszins duidelijk heb uitgelegd... alvast bedankt!

edit: dit kan helaas niet met een draaitabel, omdat de omzet ook vergeleken moet worden met het target (gegevens uit een ander tabblad)

[ Voor 9% gewijzigd door Gitarist op 11-10-2016 13:08 ]

[Elfjes]

maak een lijstje met de verschillende relevante accountmanagers en maak je sommen.als één voor één
tel vervolgens de resultaten op?

[Gitarist]

Elfjes schreef op dinsdag 11 oktober 2016 @ 15:42:
maak een lijstje met de verschillende relevante accountmanagers en maak je sommen.als één voor één
tel vervolgens de resultaten op?

Ja, dat is inderdaad de workaround voor nu, maar dat houdt in dat ik iedere keer als er een accountmanager erbij komt, ik het lijstje moet aanpassen en daarmee ook de formule moet aanpassen.

Ik hoop dat er een mogelijkheid is om de formule om te kunnen laten gaan met een dynamisch lijstje.

[Elfjes]

maak er 1 cel per account manager van en tel deze vervolgens bij elkaar op:

	A	B
1	totaal	=SOM(B2:B100)
2	acc1	=SOMMEN.ALS(<bereik>=A2)
3	acc2	=SOMMEN.ALS(<bereik>=A3)
4	acc3	=SOMMEN.ALS(<bereik>=A4)

Zoiets? zo kun je tot 100 accountmanagers aan de lijst toevoegen

[Gitarist]

Ja, dan doe je het dus eigenlijk met een tussenstap. Een manier waarmee je er natuurlijk ook wel komt, maar in mijn specifieke geval is dat niet zo handig aangezien wij werken met dagrapportages en de omzetontwikkeling in het zicht moet blijven (omzet t/m vandaag -/- omzet t/m gisteren, en dat dan ook per week).

Op die manier zou ik dan heel veel extra verwijzingen en tabbladen moeten introduceren. En m'n bestand heeft er al zo veel ;-)

Het zou handiger zijn als ik in m'n SOMMEN.ALS een criterium kan meegeven waarin de formule gewoon kijkt naar de in een bepaald bereik (in een ander tabblad) genoemde accountmanagers.
Op die manier hoef ik alleen maar het lijstje met beschikbare (/toegestane) accountmanagers actueel te houden en kan m'n formule in tact blijven.

[_Garu_]

Laat de formules lekker lopen en gebruik draaitabellen. Daar kun je met filtering ook de gewenste resultaten krijgen.

[Gitarist]

Dat gaat helaas niet, omdat de omzet ook tegenover de targets gezet moet worden. Het bronbestand bevat alleen omzetgegevens, de targets komen uit een andere bron.

Ik heb er wel mee geëxperimenteerd om een draaitabel te maken van de omzet (dat gaat op zich goed) en dan vervolgens een ander tabblad zijn informatie vanuit de draaitabel laten halen en daarin ook de targets implementeren... Dat eerste (een ander tabblad gegevens uit een (dynamische) draaitabel laten halen) is bij mij nog niet gelukt.

[_Garu_]

Dan zou je met vba naar een oplossing kunnen. Snel, mogelijk ook nog met data importeren en trend analyses. Leuk om te maken, maar meer dan je even snel hier wegtypt. Kan je it afdeling hier niet bij helpen?

[Elfjes]

Gitarist schreef op dinsdag 11 oktober 2016 @ 16:41:
Dat gaat helaas niet, omdat de omzet ook tegenover de targets gezet moet worden. Het bronbestand bevat alleen omzetgegevens, de targets komen uit een andere bron.

Ik heb er wel mee geëxperimenteerd om een draaitabel te maken van de omzet (dat gaat op zich goed) en dan vervolgens een ander tabblad zijn informatie vanuit de draaitabel laten halen en daarin ook de targets implementeren... Dat eerste (een ander tabblad gegevens uit een (dynamische) draaitabel laten halen) is bij mij nog niet gelukt.

als ik in excel naar een veld in een draaitabel refereer, komt excel met de formule DRAAITABEL.OPHALEN aanzetten. Volgens mij is deze formule dynamisch genoeg om met een dynamische draaitabel om te gaan. Heb je dat al geprobeerd?

[Tazzios]

Wat je precies wil is mij niet helemaal duidelijk dus misschien heb je hier niets aan, maar misschien ook wel.

Gitarist schreef op dinsdag 11 oktober 2016 @ 16:31:
...

Het zou handiger zijn als ik in m'n SOMMEN.ALS een criterium kan meegeven waarin de formule gewoon kijkt naar de in een bepaald bereik (in een ander tabblad) genoemde accountmanagers.
Op die manier hoef ik alleen maar het lijstje met beschikbare (/toegestane) accountmanagers actueel te houden en kan m'n formule in tact blijven.

Vert.zoeken combineren met je sommen.als ?
Met onderstaande tabel zou je ze dan kunnen samenvoegen.
manager Managergroup
jan 1
piet 2
klaas 1

Gitarist schreef op dinsdag 11 oktober 2016 @ 16:41:
Dat gaat helaas niet, omdat de omzet ook tegenover de targets gezet moet worden. Het bronbestand bevat alleen omzetgegevens, de targets komen uit een andere bron.
...

kun je de 2 tabellen niet samen krijgen met vert.zoeken om zo 1 tabel te maken waarop je ene draaitabel kan doen.

[Raznov]

Misschien SUBTOTAAL icm filters?

[Lustucru]

Zoek hier eens "excel + meerdere + criteria". Een lading topics die allemaal verschillende varianten van je probleem bespreken. Dit kan prima met een matrixformule.

Stel je hebt in A een kolom met accountmanagers. Kolommen B,C,D bevatten resp de accountmanager, een afdeling en een bedrag. Dan levert de volgende formule het totaal van bedragen voor afdeling x waarbij de accountmanager in kolom A staat:

code:

{=SOM((ISGETAL(VERGELIJKEN(B1:B10;$A$1:$A$100;0)))*(C1:C10="x")*(D1:D10))}

In mensentaal:
Bouw drie matrices op:
- waarheidstabel: komt de waarde in B voor in kolom A?
- waarheidstabel: is de waarde in C gelijk aan "x"?
- waardentabel.
Vermenigvuldig de matrices met elkaar en tel het resultaat bij elkaar op.

[ Voor 23% gewijzigd door Lustucru op 11-10-2016 21:30 . Reden: iets beknopter oplossing ]

[Gitarist]

Dank allen voor jullie reacties! Ik ga er vandaag weer mee aan de slag en zal mijn ervaringen hier weer delen