[excel] subtotaal & aantal.als

[bartbh]

In Excel heb ik een hele lijst met gegevens staan. Voor de (ongefilterde) gegevens kan ik gebruik kan van
=AANTAL.ALS('invullijst'!AE:AE;"geen*")
Om het aantal cellen die gebeginnen met "geen" te tellen.

Echter nu wil ik dit toepassen op een gefilterde lijst, maar via de formule SUBTOTAAL() is dit niet mogelijk. Hoe kan ik dan toch de formule AANTAL.ALS toepassen op een gefilterde tabel?

[Verwijderd]

Door het filtercriterium in je formule te integreren. Dan zul je wel gebruik moeten maken van een andere formule dan AANTAL.ALS; mijn voorkeur zou dan uitgaan naar SOMPRODUCT.

[pedorus]

Het lastige hier is dat alleen SUBTOTAL iets met gefilterde data doet dacht ik. Maar ik weet wel een oplossing. Stel de data staat in range a2:a10. Je zou dan kunnen kijken naar iets als:

code:

=SUMPRODUCT(SUBTOTAL(103,INDIRECT("a"&ROW(2:10))),COUNTIF(INDIRECT("a"&ROW(2:10)),"geen*"))

In jouw geval staat de data blijkbaar in een kolom. Om dan 2:65535 (of meer) te gebruiken is niet zo snel, zeker niet als je meer van dat soort formules hebt. Je kan dan de laatste rij bepalen met COUNTA, als er geen lege cellen zijn:

code:

=SUMPRODUCT(SUBTOTAL(103,INDIRECT("a"&ROW(INDIRECT("2:"&COUNTA(A:A))))),COUNTIF(INDIRECT("a"&ROW(INDIRECT("2:"&COUNTA(A:A)))),"geen*"))

Of als er wel lege cellen zijn, maar geen cellen heel hoog in het alfabet, dan kun je het volgende doen:

code:

=SUMPRODUCT(SUBTOTAL(103,INDIRECT("a"&ROW(INDIRECT("2:"&MATCH("zzzzzzzzzzzzzzzzz",A:A))))),COUNTIF(INDIRECT("a"&ROW(INDIRECT("2:"&MATCH("zzzzzzzzzzzzzzzzz",A:A)))),"geen*"))

O ja, voor de NL-functienamen zie hier, en ',' moet ';' zijn.