verschuiving in bereik subtotaal formule

[arjoderoon]

ik wil een subtotaal formule maken die in cel L7 komt te staan. die moet alle waarden in kolom L, vanaf rij 9 tot waar er maar ingevuld is, meenemen.

En daarbij het subtotaal tellen van de gefilterde waarden.

ik had daarvoor deze formule:
Code:
=SUBTOTAAL(109;VERSCHUIVING(L7;1;0;AANTALARG(A:A);1))die telt wel alle rijen tot rij 11, maar wat er in rij 19 staat ingevuld wordt niet meegenomen. (met lege regels ertussen)
(en het excel autofilter neemt rij 19 ook niet mee)


ik gebruik office 2010 (voor het geval dit van invloed is)


hoe kan ik de formule goed schrijven zodat alle waarden in kolom L meegenomen worden?

 

[wampier]

als je aantalarg gebruikt, mogen er geen gaten in de kolom zitten. Je kunt wel een eigen functie toevoegen die de laatste cel voor je vind, maar ik ken geen truc met de standaardfuncties

 

[WHER]

Indien je de laatste ingevulde cel in een kolom wil vinden, zie bijlage.

 

[arjoderoon]

Als er geen lege regels tussen zouden staan. Hoe zou ik dan de formule moeten maken zodat deze het bereik automatisch aanpast op basis van toegevoegde regels?


en wat betreft de opzoeken formule. als ik dit in een subtotaal formule mee wil nemen, kom ik er niet uit:
=subtotaal(109;J2:J**

**daar moet dan de laatst gevulde regel nummer komen die met de opzoeken formule is opgezocht.

 

[WHER]

Probeer eens:

=subtotaal(109;indirect("J2:J"&opgezochte_regelnummer))

 

[arjoderoon]

thanks. hij werkt...

 

[wampier]

leuke truc weer, maar hoe kom je er in hemels naam op om een div/0 te gebruiken als zoekvector?

 

[WHER]

Gewoon ergens (op)gepikt op een van de engelstalige fora. "Lookup" blijkt merkwaardig genoeg bestand te zijn tegen onderliggende "div/0" errors.