[F3]=SUM(IF(A3:A27=F2;IF(ISNUMBER(D3:D27);1)))

[D3]=IF(ISNA(MATCH(C3;$D$2:D2;0));C3;IF(ISNA(MATCH(C3;$D$2:D2;0))*ISNA(MATCH($A3&”|”&$B3;$A$2:A2&”|”&$B$2:B2;0));C3;IF(ISNUMBER(LOOKUP(9,99999999999999E+307;1/($A$2:A2=A3);$C$2:C2));IF(LOOKUP(9,99999999999999E+307;1/($A$2:A2=A3);$C$2:C2)<>C3;C3;”#”);”#”)))

[E5]=IF(ROWS($E$3:E3)<=$F$3;INDEX($B$3:$B$27;SMALL(IF($A$3:A27=$F$2;IF(ISNUMBER($D$3:$D$27);ROW($B$3:$B$27)-ROW($B$3)+1));ROWS($E$3:E3)));””)

[F5]=IF($E5=””;””;INDEX($D$3:$D$27;SMALL(IF($A$3:$A$27=$F$2;IF($B$3:$B$27=$E5;ROW($D$3:$D$27)-ROW($D$3)+1));COUNTIFS($E$5:E5;E5))))

Let op: Alle formules invoeren met Ctrl+Shift+Enter.

[D3], [E5], [F5] doorvoeren naar beneden.

Je wilt een item opzoeken maar het probleem is dat er dubbele waarden in kolom B staan. Als je VLOOKUP gebruikt, wordt alleen de 1-ste overeenkomst gevonden. Maar wat als je de 2-de of 3-de overeenkomst wil vinden. De oplossing is dat je een extra kolom toevoegt (Kolom A) en de dubbele items, Kolom B, van een volgnummer voorziet.

Het volgnummer kun je op de volgende manier genereren:
We gaan er van uit dat er maximaal 3 dubbele waarden voorkomen. In het gele gedeelte, Kolom H, vul je de parameters in:
[H1] Type je de waarde die je wilt zoeken. Bijvoorbeeld: ITEM 3
[H2] laat je leeg
[H3] zet je, 1
[H4] zet je, 2
[H5] zet je, 3

In A2 zet je de formule om de volgnummers te genereren.
[A2] =IF(COUNTIF($B$2:$B$14;$H$1)>1;IF(B2=$H$1;COUNT($A$1:A1)+1;””);””)
Vervolgens doorvoeren naar beneden tot A14

Tenslotte,
[H8] =H2
[H9] =IFERROR(INDEX($C$2:$C$14;MATCH(H$2&$H1;MATCH($A$2:$A$14&$B$2:$B$14;);0));””)
Formule doorvoeren naar beneden tot H12.

De truc is eigenlijk dat je het volgnummer en het item samenvoegt tot één zoekwaarde d.m.v. het Ampersand teken. Dat gebeurt in het rood gemerkte gedeelte:
H$2&$H1 wordt dus in feite 1ITEM 3. Hetzelfde gebeurt met de waarden in de kolommen A en B: $A$2:$A$14&$B$2:$B$14

Formule in:
[G1]: =IF(AND(MOD(ROW(A1);5) > 0;MOD(ROW(A1);5) < 4);A1;””)
Bijvoorbeeld 3 rijen weergeven 2 rijen overslaan.

Stel, je hebt verkoopcijfers of iets dergelijks en je wilt bepaalde maanden van een bepaalde verkoper optellen. In dit voorbeeld de maanden Februari en Mei. Tabel staat hier onder.
Let eerst op kolom I. In I1 zet je Id, en in I3 Totaal. In I2 kun je de id van de verkoper typen maar handiger is om daar een keuzelijst van te maken. Als je unieke waarden hebt in kolom A is dat een fluitje van een cent. Kies voor:

Gegevens | Gegevensvalidatie | Gegevensvalidatie | Toestaan > Lijst | Bron > selecteer de waarden in kolom A

Nu kun je gewoon een waarde in I2 kiezen middels de keuzelijst.

Formule gaat in cel I4:

=SOM(SOMMEN.ALS(INDEX($B$2:$G$10;VERGELIJKEN($I$2;$A$2:$A$10;0);0);$B$1:$G$1;{“februari”\”Mei”}))

Let even speciaal op het rode gedeelte in de formule. Daar geef je de maanden op die je wilt tellen, gescheiden door een backslash.Let ook op de Accolades { }

ADO betekent “ActiveX Data Objects“. Kun je verder vergeten. Wat kun je er mee? Gegevens ophalen uit een ander (Excel)bestand. Dat andere bestand fungeert dan als een soort database. Wat mij betreft kun je net zo goed het bestand openen en de gegevens die je nodig hebt filteren en vervolgens kopiëren om er verder mee te werken.

Excel bestand is tabellen.xlsx en de data staat op het blad shippers. Zie onderstaand screenshot. Dat bestand kun je zelf aanmaken en dan openen in Excel om het te kunnen lezen.

Wie persé die voorziening wil gebruiken hier twee voorbeelden.
Onderstaande code kopieren en plakken in blanco werkmap. Zorg dat de blanco werkmap op de voorgrond staat en dan de code laten uitvoeren.

VOORBEELD 1

Option Explicit

'***********************************************
'Set reference to:
'Microsoft ActiveX Data Objects Library
'Via: Alt+F11 | Extra | References
'***********************************************

Sub Voorbeeld_ADO_Verbinding_Voorbeeld_1()
    Dim objVerbinding As New ADODB.Connection
    Dim objGegevensSet As New ADODB.Recordset
    Dim strPad As String, strVerbind As String, strSQL As String
    
    'Path to file
    strPad = "C:\temp\tabellen.xlsx"
    
    strVerbind = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & strPad & ";HDR=Yes';"
    
    objVerbinding.Open strVerbind
    
    'Tablename is de sheetname in this example [shippers$]
    'Dollarsign is mandatory behind shippers.
    strSQL = "SELECT * From [shippers$]"
        
    objGegevensSet.Open strSQL, objVerbinding
    
    'Paste data in activesheet
    ActiveSheet.Range("A2").CopyFromRecordset objGegevensSet
    
    'Close dataset
    objGegevensSet.Close
    
    'Close connection
    objVerbinding.Close
End Sub

VOORBEELD 2

Option Explicit

'***********************************************
'Set reference to:
'Microsoft ActiveX Data Objects Library
'Via: Alt+F11 | Extra | References
'***********************************************

Sub Voorbeeld_ADO_Verbinding_Voorbeeld_2()
    Dim arrMatrix As Variant
    Dim objVerbinding As New ADODB.Connection
    Dim objGegevensSet As New ADODB.Recordset
    Dim strPad As String, strVerbind As String, strSQL As String
    Dim i As Long, j As Long
    
    'Path to file
    strPad = "C:\temp\tabellen.xlsx"
    
    strVerbind = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & strPad & ";HDR=Yes';"
    
    objVerbinding.Open strVerbind
    
    'Tablename is de sheetname in this example [shippers$]
    'Dollarsign is mandatory behind shippers.
    strSQL = "SELECT * From [shippers$]"
        
    objGegevensSet.Open strSQL, objVerbinding
    
    'Load data in Array
    arrMatrix = objGegevensSet.GetRows
    
    'Paste data from array to activesheet
    For i = 0 To UBound(arrMatrix, 2) 'Rows
        For j = 0 To UBound(arrMatrix, 1) 'Columns
            ActiveSheet.Cells(i + 2, j + 1).Value = arrMatrix(j, i)
        Next j
    Next i
    
    'Close dataset
    objGegevensSet.Close
    
    'Close connection
    objVerbinding.Close
End Sub

Verkopers hebben goed hun best gedaan en van alles verkocht en geld verdiend. Nu wil je de totalen hebben van die verkopers maar slechts als ze voldoen aan een bepaald criterium. Bijvoorbeeld: Periode = 3.

In de kolom Verkopers, Kolom B, staan de namen. Er staan echter dubbele waarden in en je wilt de naam van de verkoper slechts één keer weergeven en daarachter het totaal van periode = 3. Kijk naar de opzet van het werkblad. Resultaten weergegeven vanaf E5.

FORMULES:

In [F2] voer je handmatig de periode in.

Invoeren met Ctrl+Shift+Enter, NIET met Enter.
[F3] =SUM(IF(FREQUENCY(IF(Periode=$F$2;IF(Verkoper<>””;MATCH(Verkoper;Verkoper;0)));intRij);1))

Invoeren met Ctrl+Shift+Enter, NIET met Enter
[E5] =IF(ROWS($E$5:E5<=$F$3;INDEX(Verkoper;SMALL(IF(FREQUENCY(IF(Periode=$F$2;IF(Verkoper<>””;
MATCH(Verkoper;Verkoper;0)));intRij);intRij);ROWS($E$5:E5)));””)

Invoeren met Enter
[F5]=IF(E5<>””;COUNTIFS(Periode;$F$2;Verkoper;E5);””)

Invoeren met Enter
[G5]=IF($E5=””;””;SUMIFS(Bedrag;Verkoper;$E5;Periode;$F$2))

Namen geven: Ga naar Formules | in de groep Gedefinieerde namen |  Namen beheren

Periode
=Sheet1!$A$2:$A$40

Verkoper
=Sheet1!$B$2:$B$40

Bedrag
=Sheet1!$C$2:$C$40

intRij
=ROW(Verkoper)-ROW(INDEX(Verkoper;1;1))+1

Je hebt een controlelijst met Id, Naam en Email. Nu krijg je een nieuwe lijst met BIJNA dezelfde gegevens echter het Id ontbreekt. Als Naam en Email hetzelfde zijn kun je het Id zoeken in de controle lijst. De truc is om de Naam en het Email samen te voegen met het & teken en vervolgens te vergelijken met de Naam en het Email in de andere lijst (die je natuurlijk ook samenvoegt).

Formule invoeren met Ctrl+Shift+Enter. Niet alleen Enter.

[E2]=IFERROR(INDEX($A$2:$A$22;MATCH(F2&G2;$B$2:$B$22&$C$2:$C$22;0));””)

Kopiëren naar beneden.

Simpel voorbeeld.
Totalen berekenen van diverse verkopers. In dit voorbeeld: John, Jams, Super en Band. Beetje gekke namen maar dat boeit verder niet.
Formule komt in C11.

=SOMPRODUCT((($A$2:$A$7=A11)+($A$2:$A$7=B11));$C$2:$C$7)

Naar beneden kopiëren. Klaar

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 B met kolom C 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 A 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.