In dit artikel leren we hoe u rijen kunt tellen als dat aan meerdere criteria in Excel voldoet.
Scenario:
In eenvoudige bewoordingen, tijdens het werken met gegevenstabellen, moeten we soms de cellen tellen waarin meer dan twee bereiken aan criteria voldoen. Dit kan met behulp van de onderstaande formule.
Hoe het probleem op te lossen?
Voor dit probleem moeten we de SOMPRODUCT-functie gebruiken. Hier krijgen we twee bereiken en we hebben het aantal rijen nodig dat aan 3 criteria voldoet. De functie SOMPRODUCT retourneert de SOM van corresponderende TRUE-waarden (als 1) en negeert waarden die overeenkomen met FALSE-waarden (als 0) in de retourneert een enkele array waarbij de voorwaarden WAAR waren.
Generieke formule:
| = SOMPRODUCT ( ( rng_1 op_1 crit_1 ) + 0 , ( rng_2 op_2 crit_1 ) + 0 , rng_2 op_2 crit_1 ) + 0 ) |
rng: bereik om naar te zoeken
crit: criteria om toe te passen
op : criteria-operator, voorwaarde gegeven als operator tussen bereik en criteria
+0 : converteert booleaanse waarden naar binair (0 & 1).
Voorbeeld:
Al deze dingen kunnen verwarrend zijn om te begrijpen. Laten we deze formule dus testen door deze in het onderstaande voorbeeld uit te voeren.
Hier moeten we het aantal rijen vinden dat wordt vermeld in het bereik met 3 voorwaarden. Hier hebben we een lijst van diplomatieke ontmoetingen tussen India en de VS vanaf 2014. De tabel toont de president / premier met het landlabel en het jaar. De tabel is ook verdeeld in delen die het thuisland en de lijst met bezoekende landen vertegenwoordigen.
Onderstaande voorwaarden:
De Amerikaanse president "Barack Obama bezocht India met minder dan 2 problemen.
Gebruik de formule:
| = SOMPRODUCT (( C4:C10 = "Barack Obama") + 0, ( F4:F10 = "India") + 0, (G4:G10 < 2) + 0)) |
C4:C10="Barack Obama" : President die overeenkomt met "Barack Obama" in de bezoekerslijst.
F4:F10="India" : gastland dat overeenkomt met "India".
G4:G10<2: problemen minder dan twee.
+0 : converteert booleaanse waarden naar binair (0 & 1).
Hier wordt het bereik gegeven als celverwijzing. Druk op Enter om de telling te krijgen.
Zoals je kunt zien, bezocht de Amerikaanse president Barack Obama India, wat in 2015 gebeurde. Dit laat zien dat de formule het aantal keren haalt dat overeenkomt met de overeenkomstige matrix. Aangezien er 1 keer is dat de Amerikaanse president "Barack Obama" India heeft bezocht, waar de problemen ook gelijk zijn aan 1, wat minder is dan 2
Met gelijk aan criteria:
Bovenstaand voorbeeld was eenvoudig. Dus om het interessant te maken, zullen we tellen hoe vaak de VS India hebben gehost vanaf 2014 als door gegevens.
Onderstaande voorwaarden:
Door de VS gehost India met problemen is gelijk aan 2.
Gebruik de formule:
| =SOMPRODUCT ( ( F4:F10 = "VS") + 0, ( D4:D10 = "India") + 0, (G4:G10 = 2) + 0) |
F4:F10="VS" : gastland dat overeenkomt met "VS".
D4:D10="India" : bezoekend land dat overeenkomt met "India".
G4:G10=2 : uitgifte is gelijk aan twee.
+0 : converteert booleaanse waarden naar binair (0 & 1).
Hier wordt het bereik gegeven als celverwijzing. Druk op Enter om de telling te krijgen.
Zoals je kunt zien, zijn er 2 keer dat de VS India hebben gehost en problemen gelijk zijn aan twee. Dit laat zien dat de formule het aantal keren dat overeenkomt in de overeenkomstige matrix extraheert. Er zijn 5 keer dat de VS India hosten, maar de problemen waren 1 of 3, maar hier moeten we problemen met 2 matchen.
Met groter dan criteria:
Om het interessant te maken, zullen we tellen hoe vaak de Amerikaanse president "Donald Trump" de Indiase premier vanaf 2014 heeft gehost als door gegevens.
Onderstaande voorwaarden:
De Amerikaanse president "Donald Trump" gehost India met problemen is groter dan 1.
Gebruik de formule:
| =SOMPRODUCT ( ( C4:C10 = "Donald Trump") + 0 , ( F4 : F10 = "India") + 0 , ( G4:G10 >1 ) + 0 ) |
F4:F10="VS" : gastheer-president die overeenkomt met "Donald Trump".
D4:D10="India" : bezoekend land dat overeenkomt met "India".
G4:G10=2 : uitgifte is gelijk aan twee.
+0 : converteert booleaanse waarden naar binair (0 & 1).
Hier wordt het bereik gegeven als celverwijzing. Druk op Enter om de telling te krijgen.
Zoals je kunt zien, een keer waar de Amerikaanse president "Donald Trump" India ontving en meer dan twee uitgeeft. Dit laat zien dat de formule het aantal keren dat overeenkomt in de overeenkomstige matrix extraheert. Omdat er 2 keer was dat de Amerikaanse president "Donald Trump" India gastheer was, maar de problemen waren 1 of 3, maar hier moeten we problemen groter dan 1 hebben, wat 3 leugens is in het jaar 2019.
Met problemen die niet in criteria worden behandeld:
Om het gemakkelijk en gemakkelijk te begrijpen te maken, zullen we tellen hoe vaak de Amerikaanse president in totaal India heeft bezocht vanaf 2014, op basis van gegevens.
Onderstaande voorwaarden:
De Amerikaanse president bezocht India in totaal vanaf 2014.
Gebruik de formule:
| =SOMPRODUCT((F4:F10="India")+0,(D4:D10="VS")+0) |
F4:F10="VS" : gastland dat overeenkomt met "VS".
D4:D10="India" : bezoekend land dat overeenkomt met "India".
G4:G10=2 : uitgifte is gelijk aan twee.
+0 : converteert booleaanse waarden naar binair (0 & 1).
Hier wordt het bereik gegeven als celverwijzing. Druk op Enter om de telling te krijgen.
Zoals je kunt zien, 2 keer waar de VS India hebben bezocht en meer dan twee keer. Dit laat zien dat de formule het aantal keren dat overeenkomt in de overeenkomstige matrix extraheert. Zoals er een keer was dat de Amerikaanse president "Barack Obama" India bezocht in 2015 en een keer dat de Amerikaanse president "Donald Trump" India bezocht in het jaar 2020.
U kunt ook bereiken als criteria uitvoeren. Tel de cellen waar 2 bereiken voldoen aan de criteria. Lees hier meer over Countif met SUMPRODUCT in Excel.
Hier zijn enkele observatienotities die hieronder worden weergegeven.
Opmerkingen:
- De formule werkt alleen met getallen.
- De matrices in de formule moeten even lang zijn, omdat de formule een fout retourneert als dat niet het geval is.
- De functie SOMPRODUCT beschouwt niet-numerieke waarden als nullen.
- De functie SOMPRODUCT beschouwt de logische waarde TRUE als 1 en False als 0.
- De argumentarray moet dezelfde grootte hebben, anders retourneert de functie een fout.
- De functie SOMPRODUCT retourneert de som na het nemen van individuele producten in de bijbehorende array.
- Operators zoals is gelijk aan ( = ), kleiner dan gelijk aan ( <= ), groter dan ( > ) of niet gelijk aan ( ) kan worden uitgevoerd binnen een toegepaste formule, alleen met getallen.
Ik hoop dat dit artikel over het tellen van rijen die aan meerdere criteria in Excel voldoen, verhelderend is. Vind hier meer artikelen over telformules. Als je onze blogs leuk vond, deel het dan met je fristarts op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite
Vind de laatste rij gegevens met getallen in Excel : Zoek in een reeks tekstwaarden de laatste gegevens in Excel.
Hoe de SOMPRODUCT-functie in Excel te gebruiken?: Retourneert de SUM na vermenigvuldiging van waarden in meerdere arrays in Excel.
AANTALLEN.ALS met dynamisch criteriumbereik : Tel cellen die depstartent zijn op andere celwaarden in Excel.
AANTAL.ALS Twee criteria-overeenkomst : Tel cellen die overeenkomen met twee verschillende criteria op de lijst in Excel.
AANTALLEN MET OF Voor meerdere criteria : Tel cellen met meerdere criteria die overeenkomen met behulp van de OF-functie.
De AANTAL.ALS-functie in Excel : Tel cellen afhankelijk van andere celwaarden.
Hoe Countif te gebruiken in VBA in Microsoft Excel : cellen tellen met Visual Basic for Applications-code.
Jokertekens gebruiken in Excel : Tel cellen die overeenkomen met zinnen met behulp van de jokertekens in Excel
populaire artikels
50 Excel-snelkoppeling om uw productiviteit te verhogen : Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe te gebruikende VERT.ZOEKEN-functie in Excel : Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? : Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.
