xxx.ALS gebruiken i.c.m. Subtotaal

[klunenburg]

Beste Helpers.

Vanuit een lijst met gegevens maak ik verschillende grafieken op een apart werkblad. Die gegevens zet ik naast de grafiek en haal deze met een aantal formules uit andere werkbladen. Nu wil ik ook de autofilter functie gaan gebruiken maar deze werkt mij tegen met bepaalde formules.

Voorbeeld van goed werkende formule:
Origineel: =GEMIDDELDE('Totaaloverzicht 2008'!M4:M1000)
I.c.m. Filter: =SUBTOTAAL(1;'Totaaloverzicht 2008'!M4:M1000)

Bovenstaand werkt prima. Als ik filter in het blad Totaaloverzicht 2008 past zich automatisch de uitkomst aan, en daarmee ook de gekoppelde grafiek.

Volgende soort formules krijg ik niet werkend met filter:

=(AANTAL.ALS('Totaaloverzicht 2008'!N4:N1000;"<1"))/(AANTAL('Totaaloverzicht 2008'!N4:N1000))

=(SOM.ALS('Totaaloverzicht 2008'!N4:N1000;">0"))/(AANTAL.ALS('Totaaloverzicht 2008'!N4:N1000;">0"))

Oftewel: hoe krijg ik die ALS formules werkend met subtotalen?

In het lijstje met mogelijke formules voor de functie Subtotaal zie ik die ALS formules namelijk niet terug

Alvast bedankt!

 

[Hoornvan]

klunenburg,

Heb je het al geprobeerd met,

=SOM(AANTAL.ALS(Wat jij wil)

Het is maar een idee.

 

[klunenburg]

Sorry voor de late reactie.

Heb inmiddels de door jou voorgestelde oplossing geprobeerd maar ik krijg alleen te zien dat het een ongeldige formule is. Ik snap het idee achter jouw formule ook niet, die rekent toch niet met zogenaamde subtotalen?
Ik wil een percentage berekenen van getallen met een bepaalde voorwaarde (<1 in dit geval) tegenover het totaal aantal getallen in een bereik ('Totaaloverzicht 2008'!M4:M1000 in dit geval). Dit lukt, maar niet als ik ga filteren, dan verandert de uitkomst niet (hij rekent nog steeds met het totale bereik).

 

[klunenburg]

Nog niet opgelost

Beste helpers.

Ik heb het voorstel van Hoornvan geprobeerd maar ik kom er niet uit.

Nu heb ik een voorbeeld bestand bijgevoegd waardoor mijn vraag nog duidelijker moet worden. Hopelijk kan iemand me nu uit de brand helpen.

Bedankt.

 

[klunenburg]

Gelukt!

Het is me eindelijk gelukt. Toch met door ook de SOM formule te gebruiken zoals hoornvan zei, maar dan wat aangepast. Heb de volgende formule op internet gevonden:

{=(SOM(SUBTOTAAL(3;VERSCHUIVING(Januari!N4:N1000;RIJ(Januari!N4:N1000)-MIN(RIJ(Januari!N4:N1000));;1))*(Januari!N4:N1000<=0)))/(SUBTOTAAL(3;Januari!N4:N1000))}

Hoe de formule precies werkt weet ik niet. Heb hem proberen te verkorten maar dan werkt hij niet meer goed.

Iemand een idee hoe bovenstaande formule te verkorten?

 

[bandito bob]

De formule kan niet (veel) korter.
Het grote geheim van de formule zit is dit rode gedeelte:

=(SOM(SUBTOTAAL(3;VERSCHUIVING(Januari!N4:N1000;[COLOR="Red"]RIJ(Januari!N4:N1000)-MIN(RIJ(Januari!N4:N1000))[/COLOR];;1))*(Januari!N4:N1000<=0)))/(SUBTOTAAL(3;Januari!N4:N1000))

Dit gedeelte zorgt voor verschillende bereiken van allemaal 1 cel groot. Zo wordt elke cel appart bepaald. Hierdoor hoef je geen hulpkolom te gebruiken.

Voor jouw voorbeeld bestandje wordt de formule:

=(SOM(SUBTOTAAL(3;VERSCHUIVING(A2:A9;RIJ(A2:A9)-MIN(RIJ(A2:A9));;1))*(A2:A9<1)))/(SUBTOTAAL(3;A2:A9))

bevestigen als matrix formule met CTRL-SHIFT-ENTER.

Ik zou ook het bereik (A2:A9) een naam te geven. Zo kan je het bereik ook dynamisch maken.

 

[huijb]

@klunenberg zou je het alstublieft leesbaar wiilen houden voor de helpers, lees dit eerst eens door en dan met name het stukje over codes plaatsen.

 

[klunenburg]

Allereest sorry dat ik de code tags niet gebruikte, had die sticky wel gelezen maar over het code stukje heen gelezen denk ik.

En op de tweede plaats bedankt voor de verheldering. Ik dacht ook dat de verschuiving een tijdelijke hulptabel maakte, goed gedacht dus. Toch jammer dat Excel zó veel kan maar bepaalde dingen niet goed heeft uitgewerkt.

Bedankt voor de hulp alweer. :thumb: