Ga naar hoofdinhoud

Countifs met OR-logica voor meerdere criteria in Excel

Normaal gesproken kunt u de AANTAL.ALS-functie gebruiken om cellen te tellen op basis van een of meer voorwaarden met EN-logica in Excel. Heeft u ooit een situatie meegemaakt waarin u meer dan één waarde uit een enkele kolom of een reeks cellen moet tellen? Dit betekent tellen met meerdere voorwaarden en OR-logica. In dit geval kunt u de functies SOM en AANTAL.ALS samen toepassen of de functie SOMPRODUCT gebruiken.


Tel cellen met OR-voorwaarden in Excel

Ik heb bijvoorbeeld een reeks gegevens zoals hieronder afgebeeld, nu wil ik het nummer van het product tellen dat "potlood" of "liniaal" is, hier zal ik twee formules bespreken voor het oplossen van deze taak in Excel.

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

Om in Excel met meerdere OF-voorwaarden te tellen, kunt u de functies SOM en AANTAL.ALS gebruiken met een matrixconstante, de generieke syntaxis is:

=SUM(COUNTIF(range, {criterion1, criterion2, criterion3, …}))
  • range: Het gegevensbereik bevat de criteria waaruit u cellen telt;
  • criterion1, criterion2, criterion3…: de voorwaarden waarop u cellen wilt tellen op basis van.

Om het aantal producten te tellen dat "Potlood" of "Liniaal" is, kopieert u de onderstaande formule of voert u deze in een lege cel in en drukt u vervolgens op Enter sleutel om het resultaat te krijgen:

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


Verklaring van de formule:

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

  • {"potlood","liniaal"}: Eerst moet u alle voorwaarden in een matrixconstante als volgt verpakken: {"Pencil","Ruler"}, scheid de items door komma's.
  • AANTAL.ALS(B2:B13;{"Potlood","Liniaal"}): Deze AANTAL.ALS-functie krijgt individuele tellingen voor "Potlood" en "Liniaal", en u krijgt het resultaat als volgt: {2,3}.
  • SOM(AANTALLEN.ALS(B2:B13;{"Potlood","Liniaal"}))=SOM({2,3}): Ten slotte somt deze SOM-functie alle items in de array op en retourneert het resultaat: 5.

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

=SUM(COUNTIF(B2:B13,D2:D3))


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

Hier is nog een formule die is gemaakt door de SOMPRODUCT-functie die ook kan helpen om cellen met OR-logica te tellen. De generieke syntaxis is:

=SUMPRODUCT(1*(range ={criterion1, criterion2, criterion3, …}))
  • range: Het gegevensbereik bevat de criteria waaruit u cellen telt;
  • criterion1, criterion2, criterion3…: de voorwaarden waarop u cellen wilt tellen op basis van.

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

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


Verklaring van de formule:

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

  • B2:B13={"Potlood","Liniaal"}: Deze uitdrukking vergelijkt elk criterium "Potlood", "Liniaal" met bereikcel B2:B13. Als aan het criterium wordt voldaan, wordt WAAR geretourneerd, anders wordt een ONWAAR weergegeven, u krijgt het resultaat als volgt: {TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;TRUE,FALSE ;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE}.
  • 1*(B2:B13={"Potlood","Liniaal"}): Vermenigvuldiging converteert de logische waarden – TRUE en FALSE naar 1 en 0, dus het resultaat is 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;XNUMX}.
  • SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Ten slotte telt de SOMPRODUCT-functie alle getallen in de array op om het resultaat te krijgen: 5.

Tel cellen met meerdere sets OF-voorwaarden in Excel

Soms moet u de cellen tellen met twee of meer sets OF-voorwaarden. In dit geval kunt u SUM en AANTALLEN.ALS gebruiken met een matrixconstante of SOMPRODUCT met ISNUMBER MATCH-functies.

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

Om met slechts twee sets OF-criteria om te gaan, hoeft u alleen maar een andere matrixconstante toe te voegen aan de AANTAL.ALS-formule.

Ik heb bijvoorbeeld het gegevensbereik zoals hieronder afgebeeld, nu wil ik de mensen tellen die "potlood" of "liniaal" hebben besteld en het bedrag is <100 of>200.

Voer de volgende formule in of kopieer deze naar een cel en druk vervolgens op Enter sleutel om het resultaat te krijgen, zie screenshot:

=SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"},C2:C13,{"<100";">200"}))

Note: In de formule moet u een puntkomma gebruiken voor de tweede matrixconstante, die een verticale matrix creëert.


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

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

Neem bijvoorbeeld de onderstaande gegevens, om de mensen te tellen die "Potlood" of "Liniaal" hebben besteld en de status is ofwel "Bezorgd" of "In transit" en ondertekend door "Bob" of "Eko", moet u een complexe formule toepassen .

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

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


Verklaring 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,{"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 matrixconstante. Als de overeenkomst wordt gevonden, wordt een relatieve positie van de waarde in de array geretourneerd, anders wordt een foutwaarde weergegeven. U krijgt dus de arraylijst als volgt: {1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;# Nvt}.
  • ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}):De ISNUMBER-functie converteert de getallen naar TRUEs en foutwaarden naar FALSEs als volgt: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}.

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 worden deze drie arrays met elkaar vermenigvuldigd binnen SOMPRODUCT, dat automatisch TRUE en FALSE-waarden converteert naar 1s en 0s als onderdeel van de wiskundige bewerking als volgt: 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}).
  • Ten slotte telt de SOMPRODUCT-functie alle getallen in de array op om het resultaat te krijgen: 3.

Gebruikte relatieve functie:

  • SOM:
  • De Excel SOM-functie retourneert de som van de opgegeven waarden.
  • AANTAL.ALS:
  • De AANTAL.ALS-functie is een statistische functie in Excel die wordt gebruikt om het aantal cellen te tellen dat aan een criterium voldoet.
  • SOMPRODUCT:
  • De SOMPRODUCT-functie kan worden gebruikt om twee of meer kolommen of arrays met elkaar te vermenigvuldigen en vervolgens de som van producten te krijgen.
  • ISNUMMER:
  • De Excel ISNUMBER-functie retourneert WAAR wanneer een cel een getal bevat, en ONWAAR als dat niet het geval is.
  • BIJ ELKAAR PASSEN:
  • De Microsoft Excel MATCH-functie zoekt naar een specifieke waarde in een celbereik en retourneert de relatieve positie van deze waarde.

Meer artikelen:

  • Tel unieke numerieke waarden op basis van criteria
  • In het Excel-werkblad kunt u last hebben van een probleem waarbij het aantal unieke numerieke waarden wordt geteld op basis van een specifieke voorwaarde. Hoe kan ik bijvoorbeeld de unieke aantal-waarden van het product "T-shirt" uit het rapport tellen zoals hieronder afgebeeld? In dit artikel zal ik enkele formules laten zien om deze taak in Excel te bereiken.
  • Tel het aantal rijen met meerdere OF-criteria
  • Om het aantal rijen met meerdere criteria in verschillende kolommen te tellen, met OR-logica, kan de SOMPRODUCT-functie u helpen. Ik heb bijvoorbeeld een productrapport zoals hieronder afgebeeld, nu wil ik de rijen tellen waar het product "T-shirt" is of de kleur "Zwart" is. Hoe om te gaan met deze taak in Excel?

De beste tools voor kantoorproductiviteit

Kutools for Excel - Helpt u zich te onderscheiden van de menigte

🤖 Kutools AI-assistent: Een revolutie teweegbrengen in de data-analyse op basis van: Intelligente uitvoering   |  Genereer code  |  Aangepaste formules maken  |  Analyseer gegevens en genereer grafieken  |  Roep Kutools-functies aan...
Populaire functies: Zoek, markeer of identificeer duplicaten  |  Verwijder lege rijen  |  Combineer kolommen of cellen zonder gegevens te verliezen  |  Ronde zonder formule ...
Super VLookup: Meerdere criteria  |  Meerdere waarde  |  Over meerdere vellen  |  Fuzzy opzoeken...
Gev. Keuzelijst: Gemakkelijke vervolgkeuzelijst  |  Afhankelijke vervolgkeuzelijst  |  Multi-select vervolgkeuzelijst...
Kolom Beheerder: Voeg een specifiek aantal kolommen toe  |  Kolommen verplaatsen  |  Schakel de zichtbaarheidsstatus van verborgen kolommen in  Vergelijk Kolommen met Selecteer dezelfde en verschillende cellen ...
Uitgelichte functies: Raster focus  |  Ontwerpweergave  |  Grote formulebalk  |  Werkmap- en bladbeheer | resource Library (Auto-tekst)  |  Datumkiezer  |  Combineer werkbladen  |  Cellen coderen/decoderen  |  Stuur e-mails per lijst  |  Super filter  |  Speciaal filter (filter vet/cursief/doorhalen...) ...
Top 15 gereedschapsets12 Tekst Tools (toe te voegen tekst, Tekens verwijderen ...)  |  50+ tabel Types (Gantt Chart ...)  |  40+ Praktisch Formules (Bereken leeftijd op basis van verjaardag ...)  |  19 Invoeging Tools (QR-code invoegen, Afbeelding invoegen vanaf pad ...)  |  12 Camper ombouw Tools (Getallen naar woorden, Currency Conversion ...)  |  7 Samenvoegen en splitsen Tools (Geavanceerd Combineer rijen, Excel-cellen splitsen ...)  |  ... en meer

Kutools voor Excel beschikt over meer dan 300 functies, Ervoor zorgen dat wat u nodig heeft slechts één klik verwijderd is...

Omschrijving


Office-tabblad - Schakel lezen en bewerken met tabbladen in Microsoft Office in (inclusief Excel)

  • Een seconde om te schakelen tussen tientallen geopende documenten!
  • Verminder elke dag honderden muisklikken voor u, zeg maar dag tegen muishand.
  • Verhoogt uw productiviteit met 50% bij het bekijken en bewerken van meerdere documenten.
  • Brengt efficiënte tabbladen naar Office (inclusief Excel), net als Chrome, Edge en Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations