Generieke formule
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Samenvatting
Als u meerdere werkbladen in een werkmap naar een waarde wilt zoeken en een telling wilt retourneren, kunt u een formule gebruiken op basis van de AANTAL.ALS en INDIRECTE functies. Met wat voorlopige instellingen kunt u deze benadering gebruiken om in een hele werkmap naar een specifieke waarde te zoeken. In het getoonde voorbeeld is de formule in C5:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Context - voorbeeldgegevens
Het werkboek bevat in totaal 4 werkbladen. Blad1 , Blad2 en Blad3 bevatten elk 1000 willekeurige voornamen die er als volgt uitzien:
Uitleg
Het bereik B7: B9 bevat de bladnamen die we in de zoekopdracht willen opnemen. Dit zijn slechts tekstreeksen en we moeten wat werk verzetten om ervoor te zorgen dat ze worden herkend als geldige bladreferenties.
Deze uitdrukking werkt van binnen naar buiten en wordt gebruikt om een volledige bladreferentie op te bouwen:
"'"&B7&"'!"&"1:1048576"
De enkele aanhalingstekens worden toegevoegd om bladnamen met spaties toe te staan, en het uitroepteken is een standaardsyntaxis voor bereiken die een bladnaam bevatten. De tekst "1: 1048576" is een bereik dat elke rij in het werkblad omvat.
Nadat B7 is geëvalueerd en waarden zijn samengevoegd, retourneert de bovenstaande uitdrukking:
"'Sheet1'!1:1048576"
die in de INDIRECT-functie ingaat als het 'ref_text'-argument. INDIRECT evalueert deze tekst en retourneert een standaardverwijzing naar elke cel in Sheet1 . Dit gaat in de AANTAL.ALS-functie als bereik. Het criterium wordt gegeven als een absolute verwijzing naar C4 (vergrendeld zodat de formule in kolom C kan worden gekopieerd).
AANTAL.ALS retourneert vervolgens een telling van alle cellen met een waarde gelijk aan "mary", in dit geval 25.
Opmerking: AANTAL.ALS is niet hoofdlettergevoelig.
Bevat vs. is gelijk aan
Als u alle cellen wilt tellen die de waarde in C4 bevatten, in plaats van alle cellen die gelijk zijn aan C4, kunt u als volgt jokertekens aan de criteria toevoegen:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
AANTAL.ALS telt nu cellen met de substring "Jan" ergens in de cel.
Prestatie
Over het algemeen is het geen goede gewoonte om een bereik op te geven dat alle werkbladcellen omvat. Dit kan prestatieproblemen veroorzaken, aangezien het assortiment miljoenen en miljoenen cellen omvat. In dit voorbeeld is het probleem verergerd, aangezien de formule de functie INDIRECTE gebruikt, wat een vluchtige functie is. Vluchtige functies herberekenen bij elke wijziging van het werkblad, dus de impact op de prestaties kan enorm zijn.
Beperk het bereik indien mogelijk tot een redelijke grootte. Als u bijvoorbeeld weet dat gegevens niet na rij 1000 verschijnen, kunt u alleen de eerste 1000 rijen als volgt doorzoeken:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
