Category Archives: Excel formula

SOMPRODUCT met criterium

Je hebt een aantal artikelen die besteld worden door verschillende winkeliers. Je wil weten hoeveel de verkoop van 1 bepaald produkt heeft opgebracht. In dit voorbeeld: Tofu.

Formule in E4;

=SOMPRODUCT(–(A2:A23=E2);B2:B23:C2:C23)

Deze formule vermenigvuldigt kolom A met kolom met kolom en het resultaat optelt.

In feite krijg je dus:

Rij 5: 1 * € 18,60 * 9 = € 167,40
Rij 8: 1 * € 18,60 * 35 = € 651,00
Rij 14:1 * € 18,60 * 25 = € 465,00
Rij 21: 1 * € 18,60 * 21 = € 390,60
Totaal:                           € 1674,00

Maar hoe krijg je het voor elkaar dat alleen de rijen met “Tofu” worden berekend en wat doet die 1 eigenlijk? Daarvoor zorgt dit gedeelte:
– –(A2:A23=E2)


In kolom wordt gekeken welk produkt voldoet aan het criterium in cel E2 (Tofu). Normaliter krijg je dan een reeks van FALSE en/of TRUE. De twee minnen (– –) aan het begin zorgen er echter voor dat als Tofu gevonden wordt er een 1 (i. p. v. TRUE) wordt gegenereerd. Zoniet dan wordt een 0 (i.p.v. FALSE) gegenereerd.

Optellen alle aankopen van één klant

Klant BERGS heeft diverse producten gekocht. We willen het totaal berekenen door van al zijn gekochte producten het subtotaal (Kolom E) op te tellen.

Formule in G5.
=SUMPRODUCT(($A$2:$A$10=$G$2)*($E$2:$E$10))

In G2 kun je een validatielijst maken met alle namen van de klanten.

Gegevens | Gegevensvalidatie | Gegevensvalidatie | Toestaan > Lijst | Bron > (type in het vak ->) ALFKI;BERGS;FAMIA
Let op de puntkomma tussen de klantnamen.

Unieke lijst maken en één product uitsluiten

Je hebt een lijst waarin dubbele waarden voorkomen. Je wilt een lijst maken met unieke waarden maar één waarde wil je uitsluiten/negeren.

Formule C7

=IFERROR(INDEX($A$7:$A$28;SMALL(IF(FREQUENCY(IF($A$7:$A$28<>””;IF(1-ISNUMBER(SEARCH($B$4;$A$7:$A$28));MATCH($A$7:$A$28;$A$7:$A$28;0)));ROW($A$7:$A$28)-ROW($A$7)+1);ROW($A$7:$A$28)-ROW($A$7)+1);ROWS(C$7:C7)));””)

Dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+EnterNIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }. Let op: Plaats die accolades { } niet handmatig.

Let op: Plaats die accolades { } niet handmatig.
Doorvoeren naar beneden

Formule B3

=SUM(IF(FREQUENCY(IF($A$7:$A$28<>””;IF(1-ISNUMBER(SEARCH($B$4;$A$7:$A$28));MATCH($A$7:$A$28;$A$7:$A$28;0)));ROW($A$7:$A$28)-ROW($A$7)+1);1))

Dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+EnterNIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }. Let op: Plaats die accolades { } niet handmatig.

Let op: Plaats die accolades { } niet handmatig.

Unieke lijst genereren

Een dynamische lijst maken. Dit betekent dat, naar mate je de lijst uitbreidt en dus langer maakt, de lijst zich als het ware aanpast.

We hebben namen van landen in Kolom A. Sommige landen staan er dubbel in of zelfs driedubbel. In Kolom C willen we slechts unieke namen van landen.

Aan de slag. Zorg dat je gegevens hebt zoals in de afbeelding. Vervolgens dien je een aantal namen met daaraan gekoppeld formules te maken. Doe dat zoals hieronder beschreven:

Formulas > Name manager > New
Name: = RowVector
Refers to:

=ROW(Items)-ROW(INDEX(Items;1;1))+1

Klik: OK

Formulas > Name manager > New
Name: = Items
Refers to:

=Sheet1!$A$4:INDEX(Sheet1!$A$4:$A$20;Lrow)

Klik: OK

Formulas > Name manager > New
Name: = Lrow
Refers to: 

=MATCH(REPT(“z”;255);Sheet1!$A$4:$A$20)

Klik: OK

Tenslotte formules in de volgende cellen zetten:

Formule in C2

=SUM(IF(FREQUENCY(IF(1-(Items=””);MATCH(Items;Items;0));RowVector);1))

Dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+EnterNIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }. Let op: Plaats die accolades { } niet handmatig.

Let op: Plaats die accolades { } niet handmatig.

Formule in C4

=IF(ROWS($C$4:C4)<=$C$2;INDEX(Items;SMALL(IF(FREQUENCY(IF(1-(Items=””);MATCH(Items;Items;0));RowVector);RowVector);ROWS($C$4:C4)));””)

Ook dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+EnterNIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }. Let op: Plaats die accolades { } niet handmatig.

Let op: Plaats die accolades { } niet handmatig.
Doorvoeren naar beneden

Totalen berekenen, 2 criteria

Een paar winkels (Kolom A) hebben goede (of slechte) zaken gedaan en je ziet de resultaten per dag (Kolommen B:G in de afbeelding. De opgave dit keer is om de totalen (Kolom F) te berekenen. Er zijn 2 criteria namelijk, bedrag >= €5000 en de datum moet liggen tussen 2-9-2016 en 5-9-2016.

Formule in H8

=SUMIFS(B8:G8;$B$7:$G$7;”>=”&DATE(2016;9;2);$B$7:$G$7;”<=”&DATE(2016;9;5);B8:G8;”>”&5000)

Doorvoeren naar beneden.

Wie heeft het meest verkocht per maand/jaar

De vertegenwoordigers hebben weer eens goed hun best gedaan maar wie heeft het meeste verkocht per maand en wie is de topverkoper van het jaar?

Formule in F2

Dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+EnterNIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }. Let op: Plaats die accolades { } niet handmatig.

Let op: Plaats die accolades { } niet handmatig.

Doorvoeren naar beneden

=IFERROR(INDEX($B$1:$E$1;SMALL(IF($B2:$E2=MAX($B2:$E2);COLUMN($B$1:$E$1)-COLUMN($B$1)+1);COLUMNS($F2:F2)));””)

Unieke waarden met twee criteria

De wielrenners in de Tour de France hebben weer goed hun best gedaan. Sommige renners hebben een etappe gewonnen maar andere renners hebben meer dan 1 etappe gewonnen. Welke renners zijn dat? Ze staan in kolom D. Hiervoor is een ingewikkelde formule gebruikt en die plaats je in D2:

=IFERROR(INDEX($A$2:$A$20;SMALL(IF(FREQUENCY(IF($A$2:$A$20<>””;IF($B$2:$B$20=”yes”;IF($C$2:$C$20=”yes”;MATCH($A$2:$A$20;$A$2:$A$20;0))));ROW($A$2:$A$20)-ROW(INDEX($A$2:$A$20;1;1))+1);ROW($A$2:$A$20)-ROW(INDEX($A$2:$A$20;1;1))+1);ROWS($1:1)));””)

Dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+Enter, NIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }.

Let op: Plaats die accolades { } niet handmatig.
Doorvoeren naar beneden

Waarden optellen terwijl Id telkens verandert

Best een moeilijke klus. In de kolommen A, C, E, G staan de Id’s van de verkopers. In de kolommen B, D, F, H staan de verkoopcijfers. Je wil de verkoopcijfers van elke Id (verkoper) optellen. Natuurlijk is er een probleem, de Id’s en verkoopcijfers kunnen telkens veranderen.

Ten eerste voeg je onderstaande code toe:

1. Kopieer de onderstaande code middels Ctrl + C
2. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
3. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
4. Druk op M om een standaard module in te voegen
5. Daar waar de cursor knippert voeg je de code in middels Ctrl + V

Vervolgens heb je de volgende formules nodig. Eerst maak je 2 benoemde bereiken. Ga naar:
Formulas | Name manager | New
en geef de naam Ivec. Vervolgens:
In het vak Refers to zet je de volgende 

=ROW(INDIRECT(“1:”&COLUMNS(PNdata)))

Herhaal dit:
Naam: PNdata
Refers to:

=arrayunion(Sheet1!$A$2:$A$12;Sheet1!$C$2:$C$12;Sheet1!$E$2:$E$12;Sheet1!$G$2:$G$12)

In J1:

=SUM(IF(FREQUENCY(IF(PNdata<>””;MATCH(“~”&PNdata;PNdata&””;0));Ivec);1))

Dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+EnterNIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }. Let op: Plaats die accolades { } niet handmatig.

In J3:

=IF(ROWS($J$3:J3)<=$J$1;MIN(IF(ISNUMBER(MATCH(PNdata;$J$2:J2;0));””;PNdata));””)

Dit is een zogenaamde array formule, invoeren met: Ctrl+Shift+EnterNIET alleen Enter. Als je dit goed hebt gedaan, plaatst Excel accolades om de formule { }. Let op: Plaats die accolades { } niet handmatig.

Doorvoeren naar beneden.

In K3:

=SUMIFS(B:H;A:G;J3)

(Dit is GEEN array formule, dus gewoon invoeren met alleen Enter).
Doorvoeren naar beneden

Download bestand

Credits gaan naar: 
Code: Juan Pablo González
Spec: Aladin Akyurek
Source: https://tinyurl.com/y3b9r9qg
May 4, 2003

Top-N, unieke lijst en bedragen optellen

Op Sheet1 staan medewerkers in Kolom A. In Kolom B datums en in Kolom C staan bedragen. In Kolom A kan de naam van dezelfde medewerker meerdere keren voorkomen.

Op Sheet2 willen we de medewerker met het hoogste totaalbedrag weergeven en vervolgens de tweede medewerker etc. Een soort van Top 10 zeg maar. Bovendien geldt er een datum limiet. Bijvoorbeeld alleen resultaten ophalen tussen 29-1-2006 en 11-3-2006

Sheet1

Sheet2

Namen maken:

1. Definieer Employee en verwijs naar Sheet1!$A$2:$A$20 in de Name Manager.

2. Definieer Date en verwijs naar Sheet1!$B$2:$B$20 in de Name Manager.

3. Definieer Amount en verwijs naar Sheet1!$C$2:$C$20 in de Name Manager.

4. Definieer Ivec en verwijs naar =ROW(Employee)-ROW(INDEX(Employee,1,1))+1 in de Name Manager.

De formules op Sheet2

A3 =SUM(IF(FREQUENCY(IF(1-(Employee="");IF(ISNUMBER(Date);IF(Date>=A2;IF(Date<=B2;MATCH(Employee;Employee;0)))));Ivec);1))

A5=IF($B5="";"";INDEX(Employee;SMALL(IFERROR(IF(SUMIFS(Amount;Employee;IF(FREQUENCY(IF(1-(Employee="");IF(ISNUMBER(Date);IF(Date>=$A$2;IF(Date<=$B$2;MATCH(Employee;Employee;0)))));Ivec);Employee);Date;">="&$A$2;Date;"<="&$B$2)=$B5;Ivec);0);COUNTIFS($B$5:B5;B5))))

B5=IF(ROWS($B$5:B5)>$A$3;"";LARGE(SUMIFS(Amount;Employee;IF(FREQUENCY(IF(1-(Employee="");IF(ISNUMBER(Date);IF(Date>=$A$2;IF(Date<=$B$2;MATCH(Employee;Employee;0)))));Ivec);Employee);Date;">="&$A$2;Date;"<="&$B$2);ROWS($B$5:B5)))

In A2 en B2 van Sheet2 kun je datums zetten waarbinnen gezocht moet worden.