Een van de veelgestelde vragen op het prikbord is het gebruik van de SumIf-functie met twee verschillende voorwaarden. Helaas is het antwoord dat SumIf niet twee verschillende condities aankan.
Om aan twee voorwaarden te voldoen, moet u een nogal gecompliceerde matrixformule gebruiken. Met de add-in voor de voorwaardelijke som-wizard kunt u deze gecompliceerde formules gemakkelijk invoeren.
Hier is een Excel-werkblad met kolommen voor product, verkoper en verkoop. De gegevens staan in de cellen A2: C29.
Als u de verkopen wilt optellen, werkt een eenvoudige SUM () -functie. =SUM(C2:C29).
Veel Excellers ontdekken de SumIf-functie. Met deze functie is het vrij eenvoudig om het totaal van de verkopen voor product ABC te berekenen.=SUMIF(A2:A29,E2,C2:C29)
Het is ook gemakkelijk om het totale aantal verkopen te berekenen dat door verkoper Joe is gedaan =SUMIF(B2:B29,E2,C2:C29).
Je zou dan aannemen dat het mogelijk is om de totale verkoop van product ABC gemaakt door Joe te berekenen. Er is echter geen manier om dit te doen met de SumIf-functie. Het blijkt dat u een vrij complexe matrix of CSE-formule moet gebruiken.
Laten we eerlijk zijn - de Sum-formule is Excel 101. De SumIf-formule loopt niet ver achter qua complexiteit. De CSE-formule om het totaal aan ABC-verkopen van Joe te berekenen, is echter voldoende om zelfs mijn hoofd te laten draaien.
Het goede nieuws: Microsoft biedt de Conditional Sum Wizard waarmee zelfs een beginner complexe voorwaardelijke formules kan invoeren op basis van 1, 2 of meer voorwaarden. De Conditional Sum Wizard is een add-in. Om deze functionaliteit aan Excel toe te voegen, gaat u naar het menu Extra en selecteert u Add-ins. Schakel in het dialoogvenster Add-ins het selectievakje naast Wizard voorwaardelijke som in en kies OK. Het is mogelijk dat u op dit moment uw installatie-cd nodig heeft, omdat Microsoft de wizard niet in de standaardinstallatie opneemt.
Als de invoegtoepassing eenmaal is ingeschakeld, wordt er een Conditonal Sum… keuze onderaan het Tools-menu.
Selecteer een enkele cel in uw gegevensset en kies Extra - Voorwaardelijke som. Ervan uitgaande dat uw gegevens netjes zijn opgemaakt met een enkele rij koppen, zal Excel het bereik van uw gegevens correct raden. Kies Volgende.
Selecteer in stap 2 de kolom die u wilt optellen. In dit geval heeft de wizard al geraden dat u de eerste (en enige) numerieke kolom - Verkoop - wilt optellen. In het midden van het dialoogvenster bevinden zich drie vervolgkeuzelijsten. Deze zijn toevallig correct voor de eerste voorwaarde - Product is gelijk aan ABC, dus kies de knop Voorwaarde toevoegen.
Dan kunt u uw tweede voorwaarde toevoegen. In dit geval wilt u specificeren dat de verkoopvertegenwoordiger Joe is. Kies de pijl voor de eerste vervolgkeuzelijst. Excel biedt een alfabetische lijst van de beschikbare kolomnamen. Kies Verkoopvertegenwoordiger.
De middelste vervolgkeuzelijst is correct, maar voor de volledigheid kunt u hier zien dat u gelijk, kleiner dan, groter dan, kleiner dan of gelijk, groter dan of gelijk of niet gelijk had kunnen kiezen.
Selecteer Joe in de derde vervolgkeuzelijst.
Kies de knop Voorwaarde toevoegen.
U bent nu klaar om naar stap 3 te gaan. Druk op de knop Volgende.
In stap 3 heeft u twee keuzes. Bij de eerste keuze voert de wizard een enkele formule in met de waarden "ABC" en "Joe" hard gecodeerd in de formule. Het geeft je het antwoord, maar er is geen mogelijkheid om de formule gemakkelijk te wijzigen. Bij de tweede keuze zal Excel een nieuwe cel aanmaken met de waarde "ABC" en een nieuwe cel met de waarde "Joe". Een derde cel bevat de formule die een voorwaardelijke som maakt op basis van die twee waarden. Met deze optie kunt u nieuwe waarden in de cellen typen om het totale aantal XYZ's te zien dat door Adam is verkocht.
De wizard zal dan vragen waar u de waarde voor ABC wilt hebben. Selecteer een cel en kies Volgende. Herhaal dit terwijl de wizard u vraagt om een cel voor Joe en de formule te selecteren.
Wanneer u in de laatste stap Voltooien kiest, maakt Excel een iets andere (maar geldige) versie van de CSE-formule.
Deze formule berekent dat Joe $ 33.338 aan ABC heeft verkocht.
Als u de productinvoercel wijzigt van ABC in DEF, wordt de formule opnieuw berekend om aan te geven dat Joe $ 24.478 aan DEF heeft verkocht.
De Conditional Sum Wizard plaatst complexe formules binnen het bereik van alle Excel-eigenaren.
Extra informatie:Als u een tabel wilt maken die de verkoop van elk product door elke verkoper laat zien, is er een aantal speciale "verzorging en voeding" die u over deze formules moet weten. Typ elke verkoopvertegenwoordiger bovenaan het bereik. Typ elk product in de linkerkolom van het assortiment. Bewerk de formule van de wizard. In de onderstaande afbeelding wijst de formule naar het product in cel E6. Deze referentie moet echt $ E6 zijn. Als u de verwijzing op E6 laat staan en de formule naar kolom G kopieert, zou de formule naar F6 kijken in plaats van naar E6 en dit zou verkeerd zijn. Als u een dollarteken toevoegt vóór de E in E6, zorgt u ervoor dat de formule altijd naar het product in kolom E kijkt. De formule verwijst ook naar een verkoper in cel F5. Deze referentie moet echt F $ 5 zijn. Als je de referentie als F5 hebt achtergelaten en naar rij 7 hebt gekopieerd,de F5-referentie verandert in F6 en dit is niet goed. Als u een dollarteken toevoegt vóór het rijnummer, wordt het rijnummer vergrendeld en de verwijzing verwijst altijd naar rij 5.
In de bewerkingsmodus (selecteer de cel en druk op F2 om te bewerken), typ een $ voor de E. Typ een dollarteken voor de 5 in F5. Druk nog niet op Enter!
Deze formule is een speciaal soort formule. Als u op Enter drukt, krijgt u een 0, wat niet correct is.
In plaats van Enter te typen, houdt u de Ctrl- en Shift-toets ingedrukt terwijl u op Enter drukt. Deze magische combinatie van C trl + S hift + E nter is waarom ik deze CSE-formules noem.
Er is nog een laatste overweging voordat u de formule naar de rest van de tabel kopieert. Uw neiging zou kunnen zijn om F6 te kopiëren en in F6: G8 te plakken. Als u dit probeert, geeft Excel u het raadselachtige bericht "U kunt een deel van een array niet wijzigen". Excel klaagt dat u een CSE-formule niet in een bereik kunt plakken dat de originele CSE-formule bevat.
Dit is gemakkelijk te omzeilen. Kopieer F6. Plakken in F7: F8.
Kopieer F6: F8. Plakken in G6: G8. U krijgt een tabel met CSE-formules met totalen op basis van twee voorwaarden.
