Tel cellen die x of y bevatten in een bereik in Excel

Om cellen te tellen die de ene of de andere waarde bevatten, kunnen sommige Excel-gebruikers twee AANTAL.ALS-functies samen toevoegen om ermee om te gaan, maar deze methode telt cellen met EN-logica (bevatten zowel x als y) in plaats van logisch OR (bevatten x of j). Om dubbeltelling van de criteria te voorkomen, laat deze zelfstudie u zien hoe u een formule op basis van de SOMPRODUCT-functie in detail toepast om het aantal cellen in een specifiek bereik te tellen dat x of y bevat in Excel.

Hoe cellen te tellen die x of y in een bereik bevatten in Excel

Zoals de onderstaande schermafbeelding laat zien, is er een gegevensbereik B3: B9, om het aantal cellen in dit bereik te tellen dat "KTE" of "KTO" bevat, pas de onderstaande formule toe om het voor elkaar te krijgen.

Generieke formule

=SUMPRODUCT(--((ISNUMBER(FIND("criteria1",rng)) + ISNUMBER(FIND("criteria2",rng)))>0))

argumenten

Hoe gebruik je deze formule?

1. Selecteer een lege cel om het resultaat uit te voeren.

2. Voer de onderstaande formule in en druk op Enter sleutel om het resultaat te krijgen.

=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>0))

Hoe werken deze formules?

=SUMPRODUCT(--((ISNUMBER(FIND(D3,B3:B9)) + ISNUMBER(FIND(D4,B3:B9)))>0))

• 1. FIND(D3,B3:B9): De functie FIND controleert of de waarde "KTE" in D3 bestaat in het opgegeven bereik (B3:B9) en retourneert een array: {1,#VALUE!,#VALUE!,#VALUE!,#VALUE!,1 ,#WAARDE!}.
  In deze array geven de twee enen aan dat de eerste en de voorlaatste cellen in de lijst B1:B3 de waarde "KTE" bevatten, de #WAARDE!'s vertegenwoordigen de waarde die "KTE" niet in andere cellen vindt.
• 2. ISNUMBER{1,#VALUE!, #VALUE!, #VALUE!, #VALUE!,1, #VALUE!}: De functie ISNUMBER retourneert TURE als het getal in de array wordt ontmoet, en retourneert ONWAAR als er fouten zijn gemaakt, en retourneert uiteindelijk de nieuwe array als {TURE;FALSE;FALSE;FALSE;FALSE;TURE;FALSE}.
• 3. FIND(D4,B3:B9): Deze FIND-functie controleert ook of de waarde "KTO" in D4 bestaat in het opgegeven bereik (B3:B9), en retourneert een array: {#VALUE!,1,#VALUE!,#VALUE!, #VALUE!, #WAARDE!,#WAARDE!}.
• 4. ISNUMBER{#VALUE!,1,#VALUE!,#VALUE!, #VALUE!,#VALUE!,#VALUE!}: De ISNUMBER-functie retourneert TUUR als het ontmoetingsnummer in de array is, en retourneert ONWAAR als er fouten worden gemaakt, en retourneert uiteindelijk de nieuwe array als {FALSE;TURE;FALSE;FALSE;FALSE;FALSE;FALSE}.
• 5. {TURE;FALSE;FALSE;FALSE;FALSE;TURE;FALSE} + {FALSE;TURE;FALSE;FALSE;FALSE;FALSE;FALSE}: Hier retourneren de twee arrays het resultaat als {1;1;0;0;0;1;0}.
• 6. {1;1;0;0;0;1;0}>0: vergelijkt hier of elk getal in de array groter is dan 0 en retourneert het resultaat als {TURE;TRUE;FALSE;FALSE;FALSE;TURE;FALSE}.
• 7. --({TURE;TRUE;FALSE;FALSE;FALSE;TURE;FALSE}): Deze twee mintekens zetten “TRUE” om in 1 en zetten “FALSE” om in 0. Hier krijg je een nieuwe array als {1;1;0;0;0;1;0}.
• 8. =SUMPRODUCT({1;1;0;0;0;1;0}): De functie SOMPRODUCT telt alle getallen in de array op en retourneert het uiteindelijke resultaat in dit geval als 3.

Gerelateerde functies

Gerelateerde formules