Unieke waarden tonen, 1 criterium, totaal

14 September 2026News around the worldadmin

Geplaatst op29 augustus 2015

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] =SOM(ALS(INTERVAL(ALS(Periode=$F$2;ALS(Verkoper<>””;VERGELIJKEN(Verkoper;Verkoper;0)));intRij);1))

Invoeren met Ctrl+Shift+Enter, NIET met Enter
[E5] =ALS(RIJEN($E$5:E5<=$F$3;INDEX(Verkoper;KLEINSTE(ALS(INTERVAL(ALS(Periode=$F$2;IF(Verkoper<>””;
VERGELIJKEN(Verkoper;Verkoper;0)));intRij);intRij);RIJEN($E$5:E5)));””)

Invoeren met Enter
[F5]=ALS(E5<>””;AANTALLEN.ALS(Periode;$F$2;Verkoper;E5);””)

Invoeren met Enter
[G5]=ALS($E5=””;””;SOMMEN.ALS(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

Totalen van verkoper berekenen

14 September 2026News around the worldadmin

Geplaatst op 25 september 2015

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

SOMPRODUCT met criterium

14 September 2026News around the worldadmin

Geplaatst op 29 september 2015

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.

Optellen alle aankopen van één klant

14 September 2026News around the worldadmin

Geplaatst op 29 september 2016

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

14 September 2026News around the worldadmin

Geplaatst op 29 september 2016

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)));””)

Let op: Invoeren met: Ctrl+Shift+Enter

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))

Let op: Invoeren met: Ctrl+Shift+Enter

2 lijsten vergelijken

14 September 2026News around the worldadmin

Geplaatst op 2 oktober 2016

Je kent dat wel. Je hebt twee lijsten die gegevens bevatten. Nu wil je checken of de items in Lijst_2 voorkomen in Lijst_1. Als het lange lijsten zijn is dat een hels karwei.
Bijvoorbeeld, komt “Drachenblut Delikatessen” voor in Lijst_1? Ja (TRUE). Komt “QUICK-Stop” voor in Lijst_1? Nee (FALSE).

Formule die je daarvoor kan gebruiken is simpel:
D1 =ISNUMBER(MATCH($B2;$A$2:$A$11;0))
Doorvoeren naar beneden.

Wil je weten of een item NIET in Lijst_1 voorkomt dan gebruik je de formule:
E1 =ISNA(MATCH($B2;$A$2:$A$11;0))
Doorvoeren naar beneden.

Webscraping gegevens ophalen van een webpagina

14 September 2026Excel and VBAadmin

Geplaatst op 2 oktober 2016

Snel gegevens ophalen van het web ook wel webscraping genoemd. We surfen daarvoor naar:

https://www.autoscout24.de/lst/ford/granada?sort=standard&desc=0&ustate=N%2CU&atype=C&cy=D&ocs_listing=include&source=homepage_search-mask

en willen de prijzen van de old-timer Ford Granada downloaden. Simpel. Onderstaande code kopiëren en in een moduleblad plakken en op F5 slaan. We halen de title en de prijs op.

Lees onderstaande rode gedeelte goed

‘*****************************************************
‘Geef een verwijzing op naar:
‘Microsoft HTML Object Library
‘Te bereiken via: Alt+F11 | Extra | Verwijzingen
‘*****************************************************

Option Explicit

'Tools->Refernces Microsoft HTML Object Library

'MSDN - URLDownloadToFile function - https://msdn.microsoft.com/en-us/library/ms775123(v=vs.85).aspx
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
(ByVal pCaller      As Long, ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved    As Long, ByVal lpfnCB As Long) As Long

Sub Find_Ford_Granada()
    
    Dim fso         As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Dim sLocalFilename As String
    sLocalFilename = Environ$("TMP") & "\urlmon.html"
    
    Dim sURL        As String
    sURL = "https://www.autoscout24.de/lst/ford/granada?sort=standard&desc=0&ustate=N%2CU&atype=C&cy=D&ocs_listing=include&source=homepage_search-mask"
    
    Dim bOk         As Boolean
    bOk = (URLDownloadToFile(0, sURL, sLocalFilename, 0, 0) = 0)
    If bOk Then
        If fso.FileExists(sLocalFilename) Then
            
            'Tools->References Microsoft HTML Object Library
            Dim oHtml4 As MSHTML.IHTMLDocument4
            Set oHtml4 = New MSHTML.HTMLDocument
            
            Dim oHtml As MSHTML.HTMLDocument
            Set oHtml = Nothing
            
            'IHTMLDocument4.createDocumentFromUrl
            'MSDN - IHTMLDocument4 createDocumentFromUrl method - https://msdn.microsoft.com/en-us/library/aa752523(v=vs.85).aspx
            Set oHtml = oHtml4.createDocumentFromUrl(sLocalFilename, "")
            
            'need to wait a little whilst the document parses
            'because it is multithreaded
            While oHtml.readyState <> "complete"
                DoEvents        'do not comment this out it is required to break into the code if in infinite loop
            Wend
            Debug.Assert oHtml.readyState = "complete"
            
            Dim sTest As String
            sTest = Left$(oHtml.body.outerHTML, 100)
            Debug.Assert Len(Trim(sTest)) > 50        'just testing we got a substantial block of text, feel free to delete
            
            'You can log the information in a textfile. Uncheck the next line.
            'LogInformation (oHtml.body.outerHTML)
            
            'this is where the page specific logic now goes, here I am getting info from Autoscout page
            
            Dim htmlBrand As Object        'MSHTML.DispHTMLElementCollection
            Dim htmlPrice As Object        'MSHTML.DispHTMLElementCollection
            Set htmlBrand = oHtml.getElementsByTagName("h2")
            Set htmlPrice = oHtml.getElementsByClassName("CurrentPrice_price__Ekflz")
            
            Dim lngCounterLoop As Long
            For lngCounterLoop = 0 To htmlBrand.Length - 1
                Dim vBrand
                Dim vPrice
                Set vBrand = htmlBrand.Item(lngCounterLoop)
                Set vPrice = htmlPrice.Item(lngCounterLoop)
                
                'On Error GoTo err_chk
                If vBrand Is Nothing Or vPrice Is Nothing Then
                    Debug.Print "There are no more car brands Or prices To be found Or available"
                Else
                    Debug.Print vBrand.outerText & " - " & vPrice.outerText
                End If
                'On Error GoTo 0
            Next
        End If
    End If
    
    'err_chk:
    '   If Err.Number = 91 Then
    '      MsgBox "There was an ERROR!!! - " & Err.Number & " :  " & Err.Description & vbNewLine & "There are no more brand or price for cars available"
    ' Else
    '    MsgBox Err.Number & ":" & Err.Description
    'End If
End Sub

Option Explicit

'Tools->Refernces Microsoft HTML Object Library

'MSDN - URLDownloadToFile function - https://msdn.microsoft.com/en-us/library/ms775123(v=vs.85).aspx
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
(ByVal pCaller      As Long, ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved    As Long, ByVal lpfnCB As Long) As Long

Sub Find_Ford_Granada()
    
    Dim fso         As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Dim sLocalFilename As String
    sLocalFilename = Environ$("TMP") & "\urlmon.html"
    
    Dim sURL        As String
    sURL = "https://www.autoscout24.de/lst/ford/granada?sort=standard&desc=0&ustate=N%2CU&atype=C&cy=D&ocs_listing=include&source=homepage_search-mask"
    
    Dim bOk         As Boolean
    bOk = (URLDownloadToFile(0, sURL, sLocalFilename, 0, 0) = 0)
    If bOk Then
        If fso.FileExists(sLocalFilename) Then
            
            'Tools->References Microsoft HTML Object Library
            Dim oHtml4 As MSHTML.IHTMLDocument4
            Set oHtml4 = New MSHTML.HTMLDocument
            
            Dim oHtml As MSHTML.HTMLDocument
            Set oHtml = Nothing
            
            'IHTMLDocument4.createDocumentFromUrl
            'MSDN - IHTMLDocument4 createDocumentFromUrl method - https://msdn.microsoft.com/en-us/library/aa752523(v=vs.85).aspx
            Set oHtml = oHtml4.createDocumentFromUrl(sLocalFilename, "")
            
            'need to wait a little whilst the document parses
            'because it is multithreaded
            While oHtml.readyState <> "complete"
                DoEvents        'do not comment this out it is required to break into the code if in infinite loop
            Wend
            Debug.Assert oHtml.readyState = "complete"
            
            Dim sTest As String
            sTest = Left$(oHtml.body.outerHTML, 100)
            Debug.Assert Len(Trim(sTest)) > 50        'just testing we got a substantial block of text, feel free to delete
            
            'You can log the information in a textfile. Uncheck the next line.
            'LogInformation (oHtml.body.outerHTML)
            
            'this is where the page specific logic now goes, here I am getting info from Autoscout page
            
            Dim htmlBrand As Object        'MSHTML.DispHTMLElementCollection
            Dim htmlPrice As Object        'MSHTML.DispHTMLElementCollection
            Set htmlBrand = oHtml.getElementsByTagName("h2")
            Set htmlPrice = oHtml.getElementsByClassName("CurrentPrice_price__Ekflz")
            
            Dim lngCounterLoop As Long
            For lngCounterLoop = 0 To htmlBrand.Length - 1
                Dim vBrand
                Dim vPrice
                Set vBrand = htmlBrand.Item(lngCounterLoop)
                Set vPrice = htmlPrice.Item(lngCounterLoop)
                
                'On Error GoTo err_chk
                If vBrand Is Nothing Or vPrice Is Nothing Then
                    Debug.Print "There are no more car brands Or prices To be found Or available"
                Else
                    Debug.Print vBrand.outerText & " - " & vPrice.outerText
                End If
                'On Error GoTo 0
            Next
        End If
    End If
    
    'err_chk:
    '   If Err.Number = 91 Then
    '      MsgBox "There was an ERROR!!! - " & Err.Number & " :  " & Err.Description & vbNewLine & "There are no more brand or price for cars available"
    ' Else
    '    MsgBox Err.Number & ":" & Err.Description
    'End If
End Sub

Het resultaat in het zogenaamde immediate window (als je dit window niet ziet, te bereiken via sneltoets Ctrl+G of via View | Immediate window)

Ford Granada 2.8 i V6 Ghia Turnier Klima – SSD- 5.Gang – € 4.890
Ford Granada L – € 7.999
Ford Granada – € 2.500
Ford Granada Dorchester – € 7.999
Ford Granada I Coupé 1.7 V4 nur 67 TKMHU neuH-Zulassung – € 9.450
Ford Granada 2,8i GhiaVOLL-RESTAURIERT – € 18.300
Ford Granada – € 5.290
Ford Granada US Modell 76er Oldtimer H-Kennzeichen 5.0 V8 – € 7.900
Ford Granada GL-GHIA SERVO/SCHIEBEDACH – € 14.900
Ford Granada MK 2 – 2,0 Automatik – erst 78000 Km. – € 7.800
Ford Granada 2.3 Ghia Automatic 2.Hdn. 86 TKM – € 16.500
Ford Granada |H-Kennzeichen|Scheckheft|Klima|AHK – € 9.970
Ford Granada Granada GL / V6 2,0 Motor / 1 HAND – € 4.000
There are no more car brands or prices to be found or available

When you want to log the information in a textfile. Uncheck the next line.
‘LogInformation (oHtml.body.outerHTML)
In the code above and put the code below in the module.

Sub LogInformation(LogMessage As String)

    Dim fileNum As Integer
    Const LogFileName As String = "C:\temp\textfile.html"

    Open "C:\temp\textfile.html" For Output As #1
    Close #1
    MsgBox "Clear complete"

    fileNum = FreeFile                                ' next file number
    Open LogFileName For Append As #fileNum           ' creates the file if it doesn't exist
    Print #fileNum, LogMessage                        ' write information at the end of the text file
    Close #fileNum                                    ' close the file

End Sub

Sub LogInformation(LogMessage As String)

    Dim fileNum As Integer
    Const LogFileName As String = "C:\temp\textfile.html"

    Open "C:\temp\textfile.html" For Output As #1
    Close #1
    MsgBox "Clear complete"

    fileNum = FreeFile                                ' next file number
    Open LogFileName For Append As #fileNum           ' creates the file if it doesn't exist
    Print #fileNum, LogMessage                        ' write information at the end of the text file
    Close #fileNum                                    ' close the file

End Sub

Gegroepeerde gegevens optellen gebaseerd op 5 criteria

14 September 2026News around the worldadmin

Geplaatst op 5 oktober 2016

Indien je de bedragen in Kolom F (Amount) wil optellen gebaseerd op de 5 criteria in kolommen A:E (First – Last – Company – Year – Month), heb je wat formules nodig. Vanwege het overzicht zijn de gegevens al gegroepeerd weergegeven. Bekijk bijvoorbeeld de gegevens in Rij 2 en 3. Die zijn hetzelfde namelijk:

Andrew Fuller Tokyo Traders 2015 11
Andrew Fuller Tokyo Traders 2015 11

De twee bedragen bij elkaar opgeteld € 15,67 + € 6,19 = € 21,86
En dat record zie je staan in Rij 2 in de Kolommen H:M

Stel je voor dat de records in Kolommen A:F door elkaar staan en dat het om honderden records gaat, je kunt je dan voorstellen dat het een hele klus is om eerst alles te sorteren en vervolgens de bedragen die bij de passende records horen op te tellen. Door enkele formules in de kolommen H:M te plaatsen.

Samengevat: Tel de bedragen in Kolom F op voor elke unieke combinatie in de Rijen A:E.

Dan nu de formules. Je moet natuurlijk eerst gegevens hebben zoals hierboven. Vervolgens maak je een paar benoemde bereiken. Doe dat als volgt:

– Ga met de cursor in je tabel staan.
– Druk op Ctrl+Shift+F3 Je komt bij: Create names from selection.
– Check > Top row
– En dan OK.

Je hebt nu 5 benoemde bereiken namelijk:

First =Sheet2!$A$2:$A$45
Last =Sheet2!$B$2:$B$45
Company =Sheet2!$C$2:$C$45
Year =Sheet2!$D$2:$D$45
Month =Sheet2!$E$2:$E$45

Let op dat de formule naar Sheet2! verwijst.

Vervolgens, ga naar Formulas > Name manager > New. Vul in:
Name: RowVector
Refers to: =ROW(First)-ROW(INDEX(First;1;1))+1

Onderstaande formules invoeren met Ctrl+Shift+Enter

Invoegen met alleen Enter
M2 =IF($H2=””;””;SUMIFS(Amount;First;$H2;Last;$I2;Company;$J2;Year;$K2;Month;$L2))

Alle formules tenslotte doorvoeren naar beneden.

Unieke lijst genereren

14 September 2026News around the worldadmin

Geplaatst op 6 oktober 2016

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))

Invoegen met Ctrl+Shift+Enter
Doorvoeren naar beneden.

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)));"")

Invoegen met Ctrl+Shift+Enter
Doorvoeren naar beneden.

Totalen berekenen, 2 criteria

14 September 2026News around the worldadmin

Geplaatst op 6 oktober 2016

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.