Aantal met OF-logica voor meerdere criteria in Excel

Normaal gesproken kun je de functie AANTAL.ALS gebruiken om cellen te tellen op basis van één of meer voorwaarden met EN-logica in Excel. Heb je ooit een situatie meegemaakt waarin je meer dan één waarde moet tellen uit één kolom of een bereik van cellen? Dit betekent dat je telt met meerdere voorwaarden en OF-logica. In dit geval kun je de functies SOM en AANTAL.ALS samen gebruiken, of de functie SOMPRODUCT gebruiken.

• Cellen tellen met OF-voorwaarden in Excel
  • Cellen tellen met OF-voorwaarden door gebruik te maken van SOM en AANTAL.ALS-functies
  • Cellen tellen met OF-voorwaarden door gebruik te maken van de SOMPRODUCT-functie
• Cellen tellen met meerdere sets OF-voorwaarden in Excel
  • Cellen tellen met twee sets OF-voorwaarden door gebruik te maken van SOM en AANTAL.ALS-functies
  • Cellen tellen met meerdere sets OF-voorwaarden door gebruik te maken van de SOMPRODUCT-functie

Cellen tellen met OF-voorwaarden in Excel

Bijvoorbeeld, ik heb een bereik aan gegevens zoals in onderstaande schermafbeelding weergegeven, nu wil ik het aantal producten tellen dat “Potlood” of “Liniaal” is. Hier zal ik twee formules bespreken om deze taak in Excel op te lossen.

Cellen tellen met OF-voorwaarden door gebruik te maken van SOM en AANTAL.ALS-functies

In Excel, om te tellen met meerdere OF-voorwaarden, kun je de functies SOM en AANTAL.ALS gebruiken met een arrayconstante. De algemene syntaxis is:

• bereik: Het gegevensbereik bevat de criteria waarop je cellen telt;
• criterium1, criterium2, criterium3…: De voorwaarden waarop je cellen wilt tellen.

Om het aantal producten te tellen die “Potlood” of “Liniaal” zijn, kopieer of voer de onderstaande formule in een lege cel in, en druk vervolgens op de Enter-toets om het resultaat te krijgen:

Uitleg van de formule:

=SOM(AANTAL.ALS(B2:B13,{"Potlood","Liniaal"}))

• {"Potlood","Liniaal"}: Eerst verpak je alle voorwaarden in een arrayconstante zoals deze: {"Potlood","Liniaal"}, scheiden de items met komma’s.
• AANTAL.ALS(B2:B13,{"Potlood","Liniaal"}): Deze AANTAL.ALS-functie krijgt individuele aantallen voor “Potlood” en “Liniaal”, en je krijgt het resultaat als volgt: {2,3}.
• SOM(AANTAL.ALS(B2:B13,{"Potlood","Liniaal"}))=SOM({2,3}): Tenslotte somt deze SOM-functie alle items in de array op en geeft het resultaat: 5.

Tips: Je kunt ook celverwijzingen gebruiken voor criteria, pas de onderstaande arrayformule toe, en druk vervolgens op Ctrl + Shift + Enter toetsen tegelijkertijd om het juiste resultaat te krijgen:

Cellen tellen met OF-voorwaarden door gebruik te maken van de SOMPRODUCT-functie

Hier is een andere formule die is gemaakt met de SOMPRODUCT-functie die kan helpen om cellen te tellen met OF-logica. De algemene syntaxis is:

• bereik: Het gegevensbereik bevat de criteria waarop je cellen telt;
• criterium1, criterium2, criterium3…: De voorwaarden waarop je cellen wilt tellen.

Kopieer of voer de volgende formule in een lege cel in, en druk op de Enter-toets om het resultaat te krijgen:

Uitleg van de formule:

=SOMPRODUCT(1*(B2:B13={"Potlood","Liniaal"}))

• B2:B13={"Potlood","Liniaal"}: Deze expressie vergelijkt elk criterium “Potlood”, “Liniaal” met celbereik B2:B13. Als het criterium wordt voldaan, retourneert het WAAR, anders wordt ONWAAR weergegeven, je krijgt het resultaat 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"}): Vermenigvuldiging converteert de logische waarden – WAAR en ONWAAR naar 1 en 0, dus het krijgt het resultaat als volgt: {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}): Tenslotte somt de SOMPRODUCT-functie alle getallen in de array op om het resultaat te krijgen: 5.

Cellen tellen met meerdere sets OF-voorwaarden in Excel

Soms moet je mogelijk de cellen tellen met twee of meer sets OF-voorwaarden, in dit geval kun je zowel SOM en AANTAL.ALS met een arrayconstante gebruiken of SOMPRODUCT met ISGETAL MATCH-functies.

Cellen tellen met twee sets OF-voorwaarden door gebruik te maken van SOM en AANTAL.ALS-functies

Om alleen met twee sets OF-criteria om te gaan, hoef je alleen maar een andere arrayconstante toe te voegen aan de AANTAL.ALS-formule.

Bijvoorbeeld, ik heb het gegevensbereik zoals hieronder afgebeeld, nu wil ik de mensen tellen die “Potlood” of “Liniaal” besteld hebben en het Bedrag is ofwel <100 of >200.

Voer de volgende formule in een cel in, of kopieer deze, en druk vervolgens op de Enter-toets om het resultaat te krijgen, zie schermafbeelding:

Opmerking: In de formule moet je een puntkomma gebruiken voor de tweede arrayconstante, wat een verticale array creëert.

Cellen tellen met meerdere sets OF-voorwaarden door gebruik te maken van de SOMPRODUCT-functie

De bovenstaande formule werkt alleen voor twee sets OF-criteria, als je moet tellen met meer criteria, kan een complexe SOMPRODUCT-formule samen met ISGETAL MATCH-functies je helpen.

Neem de onderstaande gegevens als voorbeeld, om de mensen te tellen die “Potlood” of “Liniaal” besteld hebben en de Status is ofwel “Geleverd” of “Onderweg” en ondertekend door “Bob” of “Eko”, moet je een complexe formule toepassen.

Kopieer of voer de onderstaande formule in een lege cel in, en druk vervolgens op de Enter-toets om de berekening te krijgen, zie schermafbeelding:

Uitleg van de formule:

=SOMPRODUCT(ISGETAL(MATCH(B2:B13,{"Potlood","Liniaal"},0))*ISGETAL(MATCH(C2:C13,{"Geleverd","Onderweg"},0))*ISGETAL(MATCH(D2:D13,{"Bob","Eko"},0)))

ISGETAL(MATCH(B2:B13,{"Potlood","Liniaal"},0)):

• MATCH(B2:B13,{"Potlood","Liniaal"},0): Deze MATCH-functie wordt gebruikt om elke cel in bereik B2:B13 te vergelijken met de bijbehorende arrayconstante. Als er een overeenkomst wordt gevonden, retourneert het de relatieve positie van de waarde in de array, anders wordt een foutwaarde weergegeven. Dus krijg je de arraylijst als volgt: {1;#N/B;#N/B;2;#N/B;1;#N/B;2;1;#N/B;2;#N/B}.
• ISGETAL(MATCH(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 converteert de getallen naar WAARs en foutwaarden naar ONWAARs als volgt: {WAAR;ONWAAR;ONWAAR;WAAR;ONWAAR;WAAR;ONWAAR;WAAR;WAAR;ONWAAR;WAAR;ONWAAR}.

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

SOMPRODUCT(ISGETAL(MATCH(B2:B13,{"Potlood","Liniaal"},0))*ISGETAL(MATCH(C2:C13,{"Geleverd","Onderweg"},0))*ISGETAL(MATCH(D2:D13,{"Bob","Eko"},0))):

• Vervolgens worden deze drie arrays vermenigvuldigd binnen SOMPRODUCT, wat automatisch WAAR en ONWAAR-waarden converteert naar 1-en en 0-en als onderdeel van de wiskundige bewerking zoals dit: 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}).
• Tenslotte somt de SOMPRODUCT-functie alle getallen in de array op om het resultaat te krijgen: 3.

Gerelateerde functies gebruikt:

Meer artikelen: