• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Aantal cellen tellen in meerdere tabbladen adhv criteria

Status
Niet open voor verdere reacties.
L

Lindeskote

Gebruiker
Lid geworden
26 mrt 2014
Berichten
38
Beste LS,

Momenteel heb ik een bestand met 52 tabbladen (week 1 t/m 52) waarin gegevens van monteurs staan. Met de functie AANTAL kan ik het aantal cellen tellen waarin een getal staat en geen tekst.
Code: 
=AANTAL('WK1:WK52'!A2)
Dit gaat goed.

Echter nu wil ik in meerdere tabbladen kijken hoe vaak een bepaalde tekst in een (vaste) cel voorkomt per week (en voor meerdere monteurs per week).

In de bijlage heb ik een voorbeeldbestandje toegevoegd.

In het blad ''Totaal'' wil het aantal dagen ziek/scholing/TVT en vakantie tellen. De formule moet dan bijvoorbeeld kijken in de tabbladen WK1 t/m WK5 hoe vaak het woordje ''vakantie'' voorkomt in de cellen F3:F7.Bekijk bijlage Voorbeeldbestand helpmij_forum.xlsx

Hoe krijg ik dit voor elkaar?

Ik hoor het graag! Mochten er vragen zijn dan hoor ik dat graag.
 
Je zou de functie "=AANTAL.ALS" moeten gebruiken maar die ondersteund geen 3D verwijzingen.
Met behulp van een UDF kan het wel.
Code: 
Function MijnAantalAls(Bereik As Range, criteria) As Long
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        MijnAantalAls = MijnAantalAls + WorksheetFunction.CountIf(ws.Range(Bereik.Address), criteria)
    Next ws
End Function

Bekijk bijlage AANTAL.ALS 3D_Gijs.xlsm
 
Beste Gijsbert,

Bedankt, dit is precies wat ik zoek! Echter zou ik een bereik van tabbladen willen opgeven, zodat het blad totaal niet geteld wordt. Dit heeft volgens mij iets te maken met onderstaande:
Code: 
For Each ws In ThisWorkbook.Worksheets

Ik hoor het graag!
 
zo moet dat lukken
Code: 
Function MijnAantalAls(Bereik As Range, criteria) As Long
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        [COLOR="#FF0000"]If ws.Name <> "Totaal" Then[/COLOR] MijnAantalAls = MijnAantalAls + WorksheetFunction.CountIf(ws.Range(Bereik.Address), criteria)
    Next ws
End Function
 
Beste,

Dat klopt helemaal.
Je kunt het tabblad "totaal" op de volgende manier uitsluiten in de UDF door de "rood" gemarkeerde regels toe te voegen:
Code: 
Function MijnAantalAls(Bereik As Range, criteria) As Long
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        [COLOR="#FF0000"]If ws.Name <> "Totaal" Then[/COLOR]
            MijnAantalAls = MijnAantalAls + WorksheetFunction.CountIf(ws.Range(Bereik.Address), criteria)
        [COLOR="#FF0000"]End If[/COLOR]
    Next ws
End Function
Maar nog beter is de UDF aan te passen zoals onderstaand voorbeeld:

Code: 
Function MijnAantalAls(Bereik As Range, criteria) As Long
[COLOR="#0000FF"]Application.Volatile[/COLOR]
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        [COLOR="#FF0000"]If Left(ws.Name, 2) = "WK" Then[/COLOR]
            MijnAantalAls = MijnAantalAls + WorksheetFunction.CountIf(ws.Range(Bereik.Address), criteria)
        [COLOR="#FF0000"]End If[/COLOR]
    Next ws
End Function

EDIT: Blauwe regel zorgt voor continu hercalculatie
 
Laatst bewerkt:
Bedankt allen voor de reactie! Ik heb in het originele bestand meerdere tabbladen naast alleen het ''totaal''. De laatste optie van Gijsbert is dus een goeie omdat dan alle tabbladen worden geselecteerd die voldoen aan ''WK''.

Echter wanneer ik met de formule
Code: 
=MijnAantalAls(F3:F7;P2)
(hierbij zijn F3 t/m F7 maaandag t/m vrijdag en cel P2 het woordje waar hij naar moet zoeken) lijkt het alsof er alleen in het tabblad gezocht wordt waarin de formule staat en niet in de weken 1 t/m 52. Wanneer ik de formule aanklik zie ik in ieder geval dat de cellen van dat tabblad geselecteerd worden (geen ''WK'' tabblad) voor de rest geeft hij de waarde 0, terwijl er in de andere WK1 t/m WK52 wel uitkomsten zou moeten geven.

Wellicht weten jullie het probleem?


Edit: Inmiddels telt hij wel in de tabbladen WK1 t/m WK52. Echter wordt nog steeds het tabblad meegenomen waarin de formule staat. Tenminste zo lijkt het.
 
Laatst bewerkt:
Deze excercities zijn te voorkomen als je alle gegevens in 1 tabel/werkblad zet in plaats van per week.

Wil je een overzicht van een week dan heeft MS daarvoor het autofilter en het advancedfilter uitgevonden.
Het is eenvoudiger de mogelijkheden van Excel te gebruiken dan oplossingen voor overbodige problemen te bedenken.

Goed struktuteren van gegevens gaat aan fomules en VBA vooraf.
 
snb zei:
Deze excercities zijn te voorkomen als je alle gegevens in 1 tabel/werkblad zet in plaats van per week.

Wil je een overzicht van een week dan heeft MS daarvoor het autofilter en het advancedfilter uitgevonden.
Het is eenvoudiger de mogelijkheden van Excel te gebruiken dan oplossingen voor overbodige problemen te bedenken.

Goed struktuteren van gegevens gaat aan fomules en VBA vooraf.
Klik om te vergroten...

Dit zou inderdaad handig zijn mocht ik niet zo veel gegevens hebben per week. Per week heb ik namelijk een stuk of 50 monteurs, waarvan elke week gegevens in vaste cellen staan, waardoor weer gemakkelijker optellingen van gegevens zijn te doen. =SOM('WK1'!T131+'WK2'!T131+'WK3'!T131) etc. Met een week kom ik al aan de 500 regels, waardoor het onder elkaar zetten ondoenlijk wordt. Daarnaast moet het excelbestand universeel blijven voor komende jaren (werkt met koppelingen, naar andere excelbestanden afkomstig van de blackbox uitdraaien)

Het werkt momenteel, maar ben gewoon even benieuwd of er een antwoord voor is voor in de toekomst. Zo niet, dan zal ik deze post op opgelost zetten.;)
 
Ik denk dat het nog wel een aantal jaren (ca. 40) zal duren voor je de onderste rij in het werkblad hebt bereikt met je gegevens.
 
Edit: Inmiddels telt hij wel in de tabbladen WK1 t/m WK52. Echter wordt nog steeds het tabblad meegenomen waarin de formule staat. Tenminste zo lijkt het.
Klik om te vergroten...
Het lijkt inderdaad ook zo, maar de UDF zet het later om. Dus het resultaat moet kloppen.

De opmerking van snb zou ik echter wel heel serieus nemen. Het is namelijk verstandig alles in 1 database te zetten.
Ik gebruik zelf ook een plantool waarin voor +/- 250 medewerkers x 52 weken x verschillende projecten alles in 1 tabel is gegoten.
(totaal op dit moment +/- 35.000 regels)
M.B.V. draaitabellen/draaigrafieken kun je deze data dan zeer efficiënt analyseren en presenteren.
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan