COUNTIFS-functie, hoe u de celtellingsfunctie gebruikt volgens meerdere voorwaarden in Excel

De AANTAL.ALS-functie in Excel heeft veel praktische toepassingen. Dit is wat u moet weten over het gebruik van de functie AANTAL.ALS in Excel .

Microsoft Excel is een beroemd spreadsheetprogramma ter wereld met veel handige functies. De komst van software maakt het invoeren van gegevens en het berekenen van een reeks getallen eenvoudiger dan ooit. En als u Excel gebruikt, moet u zeker de functie AANTAL.ALS kennen .

De functie AANTAL.ALS in Excel wordt gebruikt om cellen te tellen die aan veel gegeven voorwaarden voldoen. De AANTAL.ALS-functie is een van de meest gebruikte statistische Excel-functies in Excel. Het is een geavanceerde functie van de AANTAL.ALS-functie die alleen cellen telt met een bepaalde voorwaarde. Bij het werken met de functie AANTAL.ALS kunnen gebruikers eenvoudig de resultaatcellen vinden die voldoen aan de voorwaarden die in het verzoek zijn opgegeven. Voorwaarden kunnen getallen, datums, tekst of cellen zijn die gegevens bevatten. In het onderstaande artikel wordt uitgelegd hoe u de AANTAL.ALS-functie in Excel gebruikt.

• Hoe gegevens in 2 Excel-kolommen te vergelijken
• Hoe u de DSUM-functie gebruikt om sommen met complexe voorwaarden in Excel te berekenen
• Hoe u de SUMIF-functie gebruikt om voorwaardelijke bedragen in Excel te berekenen
• Gebruik van de functie VERT.ZOEKEN en specifieke voorbeelden

Instructies voor het gebruik van de AANTAL.ALS-functie in Excel

De syntaxis van de functie AANTAL.ALS is =AANTAL.ALS(criteria_bereik1, criteria1, [criteria_bereik2, criteria2],…) .

Daarin:

• Criteria_bereik1 is het eerste selectiebereik waarvoor statistieken nodig zijn, een vereiste waarde.
• Criteria1 is een voorwaarde die wordt toegepast op de selectiecriteria_bereik1. De vereiste waarde kan een cel, expressie of tekst zijn.
• [criteria_range2, criteria2] zijn aanvullende selectie- en voorwaardeparen, waardoor maximaal 127 selectie- en voorwaardeparen mogelijk zijn.

Let op bij gebruik van de AANTAL.ALS-functie:

• Extra selecties moeten hetzelfde aantal rijen en kolommen hebben als het gebied criteria_range1, en kunnen van elkaar gescheiden zijn.
• Als de selectievoorwaarde naar een lege cel verwijst, beschouwt de functie AANTAL.ALS de waarde automatisch als 0.
• De karakters ? kunnen worden gebruikt. Om een ​​bepaald teken te vervangen, vervangt het *-teken een hele reeks tekens. Als je een bord moet vinden? of een echt *-teken, typ ~ voor dat teken.
• Houd er rekening mee dat de functie AANTAL.ALS niet hoofdlettergevoelig is.
• Over het algemeen moeten tekstwaarden tussen aanhalingstekens worden geplaatst, maar getallen niet. Als er echter een logische operator bij een getal is inbegrepen, moeten het getal en de operator tussen aanhalingstekens staan, zoals hieronder weergegeven:

=COUNTIFS(A1:A10,100) // count equal to 100
=COUNTIFS(A1:A10,">50") // count greater than 50
=COUNTIFS(A1:A10,"jim") // count equal to "jim"

Opmerking : aanvullende voorwaarden moeten dezelfde regels volgen.

• Wanneer u een waarde uit een andere cel in een voorwaarde gebruikt, moet de celverwijzing bij gebruik aaneengeschakeld worden met een operator. In het onderstaande voorbeeld telt AANTAL.ALS waarden in A1:A10 die kleiner zijn dan de waarde in cel B1. Houd er rekening mee dat de operator 'kleiner dan' (dit is tekst) tussen aanhalingstekens staat, maar dat de celverwijzing niet nodig is:

=COUNTIFS(A1:A10,"<"&b1) count="" cells="" less="" than="">

Opmerking : AANTAL.ALS is een van de vele functies die voorwaarden in twee delen verdelen: Bereik + criteria. Dit veroorzaakt enkele inconsistenties met andere formules en functies.

• COUNTIFS kunnen lege of niet-lege cellen tellen. De onderstaande formules tellen lege en niet-lege cellen in het bereik A1:A10:

=COUNTIFS(A1:A10,"<>") // not blank
=COUNTIFS(A1:A10,"") // blank

• De eenvoudigste manier om AANTALALS met datums te gebruiken, is door te verwijzen naar een geldige datum in een andere cel met een celverwijzing. Als u bijvoorbeeld cellen in A1:A10 wilt tellen die een datum bevatten die groter is dan een datum in B1, kunt u een formule als deze gebruiken:

=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1

Voorbeeld van het gebruik van de AANTAL.ALS-functie in Excel

1. Gegevensblad nr. 1

We hebben de onderstaande gegevenstabel om enkele verzoeken aan de tabel te doen.

Voorbeeld 1: Bereken het aantal mannelijke werknemers met 25 werkdagen.

Voer in de cel waarin we het resultaat moeten invoeren de formule =COUNTIFS(C2:C7,"Male",D2:D7,25) in en druk op Enter.

Daarin:

• C2:C7 is een verplicht gebied van 1 telling met het geslacht van de werknemer.
• Zuid is de toestand van telgebied 1.
• C2:C7 is het tweede telgebied met de werkdag van de werknemer.
• 25 is de toestand van de regio van telling 2.

Hierdoor hebben we 2 medewerkers, Nam, met 25 werkdagen.

Voorbeeld 2: Bereken het aantal mannelijke werknemers wiens vrije dag 0 is.

Voer in het invoervak ​​voor de formule =COUNTIFS(C2:C7,"Nam",E2:E7,0) in en druk op Enter.

Uit de resultaten blijkt dat 1 mannelijke werknemer 0 vrije dagen heeft.

Voorbeeld 3: Bereken het aantal mannelijke werknemers dat 25 dagen werkt en minder dan of gelijk aan 2 dagen vrij heeft.

We voeren de formule =AANTAL.ALS(C2:C7,"Male",D2:D7,25,E2:E7,"<> in en drukken vervolgens op Enter.

Hierdoor zijn er 2 mannelijke medewerkers die voldoen aan de eisen van een vrije dag korter dan of gelijk aan 2 dagen.

2. Gegevensblad nummer 2

Voorbeeld 1: Bereken het totaal aan Taiwanese producten met een eenheidsprijs van minder dan 200.000 VND.

In het resultaatinvoervak ​​voeren we de berekeningsformule =COUNTIFS(B2:B7,"*Taiwan",C2:C7,"<> in en drukken vervolgens op Enter.

Het resultaat was 2 Taiwanese producten die aan de eisen voldeden.

Voorbeeld 2: Bereken het totaal aan Taiwanese producten met eenheidsprijzen tussen 100.000 VND en 150.000 VND.

Voer in het resultaatinvoervak ​​de formule =AANTAL.ALS(B2:B7,"*Taiwan",C2:C7,">100000",C2:C7,"<> in en druk vervolgens op Enter.

Als gevolg hiervan zullen er twee Taiwanese producten verkrijgbaar zijn met een prijs tussen 100.000 VND en 150.000 VND.

Hoe u de AANTAL.ALS-functie gebruikt in combinatie met SOM in Excel

De AANTAL.ALS-functie is in wezen een complexere versie van de AANTAL.ALS-functie. Het belangrijkste voordeel dat COUNTIFS heeft ten opzichte van COUNTIF is dat het meerdere voorwaarden en bereiken ondersteunt.

U kunt echter ook een bereik en een enkele voorwaarde definiëren voor de functie AANTAL.ALS, net zoals u deed met de functie AANTAL.ALS.

Een belangrijk ding dat u moet weten over de functie AANTAL.ALS voordat u deze gebruikt, is dat deze meer doet dan alleen de resultaten optellen van de cellen die voldoen aan de criteria voor elk celbereik.

Als u twee voorwaarden voor twee bereiken heeft, worden de cellen in het eerste bereik feitelijk twee keer gefilterd: één keer via de eerste voorwaarde en vervolgens via de tweede voorwaarde. Dit betekent dat de COUTNIFS-functie alleen waarden retourneert die aan beide voorwaarden voldoen, binnen het opgegeven bereik.

U kunt de functionaliteit van de AANTAL.ALS-functie beter begrijpen door het onderstaande voorbeeld te bestuderen.

Voorbeeld van de AANTALFS-functie in Excel

Naast de kleur van de appels is er ook een kolom waarin de grootte ervan wordt beschreven. Het uiteindelijke doel in dit voorbeeld is om het aantal grote rode appels te tellen.

1. Selecteer de cel waarin u de resultaten wilt weergeven. (In dit voorbeeld wordt in het artikel het grote aantal rode appels in cel E3 weergegeven).

2. Ga naar de formulebalk en voer de onderstaande formule in:

=COUNTIFS(A3:A11, "Red", B3:B11, "Big")

3. Met deze opdracht controleert de formule de cellen A3 tot en met A11 op de voorwaarde "Rood". Cellen die deze test doorstaan, worden vervolgens opnieuw getest in het bereik B3 tot B11 voor de ‘Big’-toestand .

4. Druk op de Enter-knop.

5. Excel telt nu het aantal grote rode appels.

Voorbeeldresultaten van de Excel-AANTAL.ALS-functie

Kijk hoe de formule cellen telt die zowel de rode als de grote kenmerken hebben. De formule neemt de cellen A3 tot en met A11 en test ze om het rode resultaat te vinden. Cellen die aan deze voorwaarde voldoen, worden vervolgens opnieuw getest aan de hand van de volgende voorwaarde in het tweede bereik, in dit geval de grote voorwaarde.

Concluderend kunnen we stellen dat de bereiken en condities na de eerste range en conditie het telfilter steeds nauwer maken en niet onafhankelijk van elkaar zijn. Het eindresultaat van het recept is dus rode en grote appels. Je kunt het aantal rode of grote appels tellen door de AANTAL.ALS-functie te combineren met de SOM-functie .

1. Selecteer de cel waarin u de resultaten van de formule wilt weergeven. (In dit voorbeeld gebruikt het artikel cel E6).

2. Voer de onderstaande formule in:

=SUM(COUNTIF(A3:A11, "Red"), COUNTIF(B3:B11, "Big"))

3. Deze formule telt de cellen met rode appels, vervolgens het aantal cellen met grote appels en ten slotte worden deze twee getallen bij elkaar opgeteld.

4. Druk op de Enter-knop.

5. Excel zal nu het aantal grote of rode appels tellen en weergeven.

Combineer de AANTAL.ALS-functie met de SOM-functie

Hierboven staan ​​2 gegevenstabellen met gebruik van de AANTAL.ALS-functie in Excel. Gebruikers moeten de voorwaarden schrijven die bij het conditiegegevensgebied horen, zodat de functie AANTAL.ALS correct kan worden bepaald.

Veelvoorkomende fouten oplossen bij het gebruik van de AANTAL.ALS-functie in Excel

COUNTIFS werkt niet bij het tellen van tekstwaarden

Bij het tellen van tekstreeksen moeten deze tussen aanhalingstekens worden geplaatst. Anders kan de functie AANTAL.ALS de tekstreeks niet tellen en wordt de waarde 0 geretourneerd. In de volgende voorbeeldafbeelding is de tekstreeks niet tussen aanhalingstekens geplaatst. Daarom retourneert deze formule 0.

Om deze fout op te lossen, hoeft u alleen maar de formule exact te herschrijven: =AANTAL.ALS(E5:E12, "Auto")

COUNTIFS werkt niet omdat de bereikreferentie onjuist is

Wanneer u meer dan één criterium gebruikt in de functie AANTAL.ALS, moet het celbereik voor de andere criteria hetzelfde aantal cellen hebben. Anders werkt de functie AANTAL.ALS niet.

Laten we zeggen dat we in dit voorbeeld de autoverkopen in Austin willen tellen. De ingevoerde formule is =AANTAL.ALS(E5:E12,"Auto",D5:D11,"Austin"). Als u de formule goed bekijkt, ziet u dat het eerste criteriumbereik hier E5:E12 is, maar het tweede criteriumbereik D5:D11. Het aantal cellen in het bereik voor dit criterium is niet hetzelfde.

Druk nu op Enter , de formule retourneert de #WAARDE! .

Herschrijf de formule als volgt correct: =AANTAL.ALS(E5:E12,"Auto",D5:D12,"Austin")

COUNTIFS werkt niet vanwege een fout in de formule

Als de formule niet correct is ingevoegd, werkt de functie AANTAL.ALS niet. Wanneer u een wiskundige operator gebruikt, zoals groter dan (>), kleiner dan (<), equal to="" (=")" en "not="" equals=""> <> ), zowel de operator als de numeriek criterium moet in dezelfde vergelijking worden ingevoerd. Als u bijvoorbeeld een omzet van meer dan $ 100.000 wilt vinden, voegt u de volgende formule in:

=COUNTIFS(F5:F12,">" 100000)

Hier wordt alleen de operator in de vergelijking ingevoegd, er is geen numeriek criterium.

Als u op Enter drukt, wordt het berichtvenster van Microsoft Excel weergegeven: "Er is een probleem met deze formule".

Het probleem oplossen. Typ de exacte formule:

=COUNTIFS(F5:F12,">100000")

Nu hebben we zowel de operator als de criteria tussen haakjes ingevoerd. Nu zal deze formule de vereiste hoeveelheid retourneren.

Druk op Enter.

Als gevolg hiervan ontvangt u een verkoopbedrag van meer dan 100.000 USD.

Ik wens je succes!