AANTAL.ALS met OF-logica voor meerdere criteria in Excel

Normaal gesproken gebruikt u de functie AANTAL.ALS om cellen te tellen op basis van één of meer voorwaarden met EN-logica in Excel. Maar hebt u ooit een situatie meegemaakt waarin u meerdere waarden uit één kolom of celbereik moet tellen? Dan gaat het namelijk om meerdere voorwaarden met OF-logica. In dat geval combineert u de functies SOM en AANTAL.ALS, of u gebruikt de functie SOMPRODUCT.

• Tel cellen met OF-voorwaarden in Excel
  • Tel cellen met OF-voorwaarden met behulp van de functies SOM en AANTAL.ALS
  • Tel cellen met OF-voorwaarden met behulp van de functie SOMPRODUCT
• Tel cellen met meerdere reeksen OF-voorwaarden in Excel
  • Tel cellen met twee reeksen OF-voorwaarden met behulp van de functies SOM en AANTAL.ALS
  • Tel cellen met meerdere reeksen OF-voorwaarden met behulp van de functie SOMPRODUCT

Tel cellen met OF-voorwaarden in Excel

Stel dat u een gegevensreeks heeft zoals op de onderstaande screenshot. U wilt nu het aantal producten tellen dat „Potlood” of „Liniaal” is. Hier bespreken we twee formules om deze taak in Excel op te lossen.

Tel cellen met OF-voorwaarden met behulp van de functies SOM en AANTAL.ALS

In Excel kunt u bij meerdere OF-voorwaarden de functies SOM en AANTAL.ALS combineren met een matrixconstante. De algemene syntaxis is:

• range: De Gegevensbereik bevat de criteria waaruit u cellen telt;
• criterium1, criterium2, criterium3…: De voorwaarden waarmee u cellen wilt tellen.

Kopieer of voer de onderstaande formule in een lege cel in en druk vervolgens op Enterom het resultaat te verkrijgen:

Uitleg van de formule:

=SUM(COUNTIFS(B2:B13,{"Pencil",„Ruler"}))

• {"Potlood",„Liniaal"}: Verpak eerst alle voorwaarden in een matrixconstante zoals deze: {"Potlood",„Liniaal"}, en scheid de items met komma's.
• AANTAL.ALS(B2:B13,{"Potlood",„Liniaal"}): Deze AANTAL.ALS-functie levert afzonderlijke tellingen op voor ‘Potlood’ en ‘Liniaal’, met als resultaat: {2;3}.
• SOM(AANTAL.ALS(B2:B13,{"Potlood",„Liniaal"}))=SOM({2,3}): Tot slot telt de SOM-functie alle items in de matrix op en geeft het resultaat: 5.

Tips: U kunt ook celverwijzingen gebruiken als criteria. Pas dan de onderstaande matrixformule toe en druk tegelijkertijd op Ctrl + Shift + Enter om het juiste resultaat te verkrijgen:

Tel cellen met OF-voorwaarden met behulp van de functie SOMPRODUCT

Hier is nog een formule die gebruikmaakt van de functie SOMPRODUCT en ook helpt bij het tellen van cellen met OF-logica. De algemene syntaxis is:

• range: De Gegevensbereik bevat de criteria waaruit u cellen telt;
• criterion1, criterion2, criterion3…: De voorwaarden waarmee u cellen wilt tellen.

Kopieer of voer de volgende formule in een lege cel in en druk op Enterom het resultaat te verkrijgen:

Uitleg van de formule:

=SUMPRODUCT(1*(B2:B13={"Pencil",„Ruler"}))

• B2:B13={"Potlood",„Liniaal"}: Deze expressie vergelijkt elk van de criteria „Potlood” en „Liniaal” met het celbereik B2:B13. Als een criterium overeenkomt, wordt WAAR geretourneerd; anders ONWAAR. Het resultaat is dan als volgt: {WAAR,ONWAAR;ONWAAR,ONWAAR;ONWAAR,ONWAAR;ONWAAR,WAAR;ONWAAR,ONWAAR;WAAR,ONWAAR;ONWAAR,ONWAAR;ONWAAR,WAAR;ONWAAR,ONWAAR;ONWAAR,ONWAAR;ONWAAR,WAAR;ONWAAR,ONWAAR}.
• 1*(B2:B13={"Potlood",„Liniaal"}): Door vermenigvuldiging worden logische waarden – WAAR en ONWAAR – omgezet naar respectievelijk 1 en 0, wat resulteert in een matrix zoals deze: {1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}.
• SOMPRODUCT(1*(B2:B13={"Potlood",„Liniaal"}))= SOMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Tot slot telt de SOMPRODUCT-functie alle getallen in de matrix op om het eindresultaat te verkrijgen: 5.

Tel cellen met meerdere reeksen OF-voorwaarden in Excel

Soms moet u cellen tellen die voldoen aan twee of meer reeksen OF-voorwaarden. In dat geval kunt u óf SOM en AANTAL.ALS combineren met een matrixconstante, óf SOMPRODUCT gebruiken in combinatie met de functies ISGETAL en VERGELIJKEN.

Tel cellen met twee reeksen OF-voorwaarden met behulp van de functies SOM en AANTAL.ALS

Voor slechts twee reeksen OF-criteria hoeft u eenvoudigweg een tweede matrixconstante toe te voegen aan de AANTAL.ALS-formule.

Stel dat ik het gegevensbereik heb zoals op de onderstaande screenshot. Ik wil nu het aantal personen tellen dat „Potlood” of „Liniaal” heeft besteld en waarvan het bedrag 200 is.

Voer de volgende formule in een cel in of kopieer deze en druk vervolgens op Enterom het resultaat te verkrijgen (zie screenshot):

: Gebruik in de formule een puntkomma voor de tweede matrixconstante, zodat er een verticale matrix ontstaat.

Tel cellen met meerdere reeksen OF-voorwaarden met behulp van de functie SOMPRODUCT

De bovenstaande formule werkt alleen voor twee reeksen OF-criteria. Moet u tellen op basis van meer criteria? Dan helpt een complexe SOMPRODUCT-formule in combinatie met de functies ISGETAL en VERGELIJKEN u verder.

Neem de onderstaande gegevens als voorbeeld: om het aantal personen te tellen dat „Potlood” of „Liniaal” heeft besteld, met een status van „Geleverd” of „Onderweg”, én waarvan de ontvangst is ondertekend door „Bob” of „Eko”, hebt u een complexe formule nodig.

Kopieer of voer de onderstaande formule in een lege cel in en druk vervolgens op Enterom de berekening te verkrijgen (zie screenshot):

Uitleg van de formule:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil",„Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered",„In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob",„Eko"},0)))

ISNUMBER(MATCH(B2:B13,{"Pencil",„Ruler"},0)):

• VERGELIJKEN(B2:B13,{"Potlood",„Liniaal"},0): Deze VERGELIJKEN-functie vergelijkt elke cel in het bereik B2:B13 met de bijbehorende matrixconstante. Als er een overeenkomst wordt gevonden, retourneert de functie de relatieve positie van die waarde in de matrix; anders verschijnt een foutwaarde. Het resultaat is de volgende matrix: {1;#N/B;#N/B;2;#N/B;1;#N/B;2;1;#N/B;2;#N/B}.
• ISGETAL(VERGELIJKEN(B2:B13,{"Potlood",„Liniaal"},0))= ISGETAL({1;#N/B;#N/B;2;#N/B;1;#N/B;2;1;#N/B;2;#N/B}): De ISGETAL-functie zet getallen om in WAAR-waarden en foutwaarden in ONWAAR-waarden, zoals hier: {WAAR;ONWAAR;ONWAAR;WAAR;ONWAAR;WAAR;ONWAAR;WAAR;WAAR;ONWAAR;WAAR;ONWAAR}.

De bovenstaande logica kan ook worden toegepast op de tweede en derde ISNUMBER-expressies.

SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil",„Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered",„In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob",„Eko"},0))):

• Vervolgens vermenigvuldigt SOMPRODUCT deze drie matrices met elkaar, waarbij WAAR- en ONWAAR-waarden automatisch worden omgezet naar respectievelijk 1-en en 0-en als onderdeel van de wiskundige bewerking, zoals hier: SOMPRODUCT({1;0;0;1;0;1;0;1;1;0;1;0} * {1;1;0;0;1;1;0;1;1;0;1;1} * {1;0;0;0;0;0;0;1;0;0;1;0}) = SOMPRODUCT({1;0;0;0;0;0;0;1;0;0;1;0}).
• Tot slot telt de SOMPRODUCT-functie alle getallen in de matrix op om het resultaat te verkrijgen: 3.

Gebruikte relatieve functie:

Meer artikelen: