[Excel 2013] Uitsluitingen op aantalarg

[Angermanagement]

Mijn vraag
Hallo allemaal,

Op dit moment ben ik bezig om een rooster te fabriceren, echter loop ik vast op 1 formule.
Lay-out:
In een cel zijn meerdere keuzemogelijkheden, te weten "Ja", "Nee", "Vrije dag" etc.
Nu heb ik de AANTALARG gebruikt (bijv: AANTALARG(A1:A56)) om een optelling te maken van het totaal aantal mensen die aanwezig is. Op dit moment telt hij alleen alle invoer bij elkaar op, dus ongeacht wat er staat.
Alleen wil ik uiteraard zaken als "Bijzonder verlof" en/of "Vrije dag" uit kunnen sluiten.

Op 1 of andere manier krijg ik dat niet verwerkt in de formule.

Relevante software en hardware die ik gebruik
Excel 2013

Wat ik al gevonden of geprobeerd heb
Gezocht in openbare bronnen, ik heb geprobeerd om de formules te nesten, alleen schijnt dit ook niet te werken (krijg een #Waarde). Ben al twee dagen bezig om dit op te kunnen lossen. Normaal kan ik redelijk goed met Excel overweg, maar juist dit soort specifieke dingen loop ik op vast.

Dank alvast voor jullie hulp!

[heuveltje]

aantallen.als is denk ik wat je zoekt
evt iets als AANTALARG - AANTALLEN.ALS(A1:56;"vrije dag") ?

[ Voor 25% gewijzigd door heuveltje op 02-02-2018 09:40 ]

[Angermanagement]

heuveltje schreef op vrijdag 2 februari 2018 @ 09:39:
aantallen.als is denk ik wat je zoekt
evt iets als AANTALARG - AANTALLEN.ALS(A1:56;"vrije dag") ?

Dank voor de snelle reactie, fantastisch!

Dit werkt, voor 1 criteria.
Hoe kan ik de overige criteria ook toevoegen?
Dus bijv. "Roostervrije dag", "Ziek" en "Training"?

Want als ik na "vrije dag" dan vervolgens ; type, en dan vervolgens de formule opnieuw maak (dus A1:A56;"Ziek") dan trekt hij hem er niet meer af maar telt hij hem wel weer gewoon bij het totaal op.

Nu is de formule op dit moment:
AANTALARG(A1:A56)-AANTALLEN.ALS(A1:A56; "Vrije dag")
Daar wil ik dan nog Roostervrij, ziek en training bij toevoegen als criteria om niet mee te tellen in het totaal.

[Roodey]

Kun je geen hulpkolom gebruiken?

Hierin zet je, in mijn geval kolom C:
=OF(B6="Ja";B6="Nee")

Dan wordt je formule (bereik loopt van B6:C12):
=AANTALARG(B6:B12)-AANTALLEN.ALS(C6:C12;ONWAAR)

[Angermanagement]

Roodey schreef op vrijdag 2 februari 2018 @ 10:03:
Kun je geen hulpkolom gebruiken?

Hierin zet je, in mijn geval kolom C:
=OF(B6="Ja";B6="Nee")

Dan wordt je formule (bereik loopt van B6:C12):
=AANTALARG(B6:B12)-AANTALLEN.ALS(C6:C12;ONWAAR)

Dank voor de snelle reactie!

Deze formule werkt helaas niet bij mij.
Ik heb op een ander tabblad geprobeerd een hulpkolom te maken en daar naar te verwijzen.
Dit pakt hij echter niet op.

Wellicht is dit makkelijker uitleggen voor je:

Mijn bereik loopt van E4:E26
Dit geldt ook voor de kolommen F,G,H,I (maandag, dinsdag, woensdag, donderdag, vrijdag)

Om alles gewoon op te tellen, is mijn formule:
AANTALARG(E4:E56)

Nu wil ik een aantal criteria uitsluiten te weten:
Roostervrij
Ziek
Vrije dag
Cursus

Met het voorbeeld van hierboven, kreeg ik het voor elkaar om 1 criteria uit te sluiten.
Die formule werd:
AANTALARG(E4:E26) - AANTALLEN.ALS(E4:E26;"vrije dag")

Het is mij nog niet gelukt om hier meerdere criteria aan te hangen.

Nu heb ik een tabblad "hulpkolom" gemaakt, en gewoon in A1 de volgende regel gemaakt:
=OF(Tabblad1!E4="Roostervrij";Tabblad1!E4="Vrije dag") etc.

Dit werkt dus (helaas) niet.

[Roodey]

Angermanagement schreef op vrijdag 2 februari 2018 @ 10:17:
[...]

Dank voor de snelle reactie!

Deze formule werkt helaas niet bij mij.
Ik heb op een ander tabblad geprobeerd een hulpkolom te maken en daar naar te verwijzen.
Dit pakt hij echter niet op.

Wellicht is dit makkelijker uitleggen voor je:

Mijn bereik loopt van E4:E26
Dit geldt ook voor de kolommen F,G,H,I (maandag, dinsdag, woensdag, donderdag, vrijdag)

Om alles gewoon op te tellen, is mijn formule:
AANTALARG(E4:E56)

Nu wil ik een aantal criteria uitsluiten te weten:
Roostervrij
Ziek
Vrije dag
Cursus

Met het voorbeeld van hierboven, kreeg ik het voor elkaar om 1 criteria uit te sluiten.
Die formule werd:
AANTALARG(E4:E26) - AANTALLEN.ALS(E4:E26;"vrije dag")

Het is mij nog niet gelukt om hier meerdere criteria aan te hangen.

Nu heb ik een tabblad "hulpkolom" gemaakt, en gewoon in A1 de volgende regel gemaakt:
=OF(Tabblad1!E4="Roostervrij";Tabblad1!E4="Vrije dag") etc.

Dit werkt dus (helaas) niet.

Als je bereik t/m J loopt. Die mis ik in je reactie. Maar 5 werkdagen kom ik t/m J.
Dan is je hulpkolom K.
In K4 zet je formule:
=OF(E4="Ja";E4="Nee")

Deze trek je door naar beneden.

Daarna kun je de volgende formule toepassen:
=AANTAL.ALS(E4:E26;WAAR)

Wil je dit eens proberen?

[Angermanagement]

Roodey schreef op vrijdag 2 februari 2018 @ 10:39:
[...]


Als je bereik t/m J loopt. Die mis ik in je reactie. Maar 5 werkdagen kom ik t/m J.
Dan is je hulpkolom K.
In K4 zet je formule:
=OF(E4="Ja";E4="Nee")

Deze trek je door naar beneden.

Daarna kun je de volgende formule toepassen:
=AANTAL.ALS(E4:E26;WAAR)

Wil je dit eens proberen?

Verander ik "Ja" en "Nee" dan in de criteria waarvan ik wil dat ze uitgesloten worden?
Want hoe weet het bestand anders welke criteria wel en niet opgeteld mogen worden?

[Icephase]

O.M.G...

Waarom gebruik je niet gewoon de functie AANTALLEN.ALS voor de categorie die je WEL wilt zien? En anders maak je een draaitabel, met bijvoorbeeld een slicer. Is erg simpel, Excel leidt je er in feite gewoon doorheen.

Niet te moeilijk denken!

[Roodey]

Angermanagement schreef op vrijdag 2 februari 2018 @ 11:03:
[...]

Verander ik "Ja" en "Nee" dan in de criteria waarvan ik wil dat ze uitgesloten worden?
Want hoe weet het bestand anders welke criteria wel en niet opgeteld mogen worden?

Nee, de formule zo laten zoals ik in mijn vorige post aangaf.
Wat doen het?
Het geeft WAAR terug in de situaties die jij mee wilt tellen. Ik ga ervan uit dat dit "Ja" en "Nee" zijn.
Vervolgens met AANTAL.ALS gaan we de WAAR's tellen.

[Angermanagement]

Icephase schreef op vrijdag 2 februari 2018 @ 11:05:
O.M.G...

Waarom gebruik je niet gewoon de functie AANTALLEN.ALS voor de categorie die je WEL wilt zien? En anders maak je een draaitabel, met bijvoorbeeld een slicer. Is erg simpel, Excel leidt je er in feite gewoon doorheen.

Niet te moeilijk denken!

Omdat ik meerdere categorieën wel wil laten zien, en ik op 1 of andere manier niet meerdere criteria in de AANTALLEN.ALS neer kan zetten.

Roodey schreef op vrijdag 2 februari 2018 @ 11:05:
[...]


Nee, de formule zo laten zoals ik in mijn vorige post aangaf.
Wat doen het?
Het geeft WAAR terug in de situaties die jij mee wilt tellen. Ik ga ervan uit dat dit "Ja" en "Nee" zijn.
Vervolgens met AANTAL.ALS gaan we de WAAR's tellen.

Mmm nee dit werkt ook niet, want hij maakt geen koppeling naar de hulpkolom dan.
Ik begrijp er nu echt helemaal niets meer van.

Is er niet een kant en klare formule die ik kan kopiëren en plakken?

Criteria die mee moeten tellen:
Roosterochtend
Roostermiddag
Thuiswerken
Ja

Criteria die niet mee moeten tellen:
Ziek
Vakantie
Roostervrij
Bijzonder verlof

Bereik:
Maandag: E4:E26
Dinsdag: F4:F26
Woensdag: F4:F26
(T/m vrijdag).

[Roodey]

Angermanagement schreef op vrijdag 2 februari 2018 @ 11:11:
[...]

Omdat ik meerdere categorieën wel wil laten zien, en ik op 1 of andere manier niet meerdere criteria in de AANTALLEN.ALS neer kan zetten.


[...]

Mmm nee dit werkt ook niet, want hij maakt geen koppeling naar de hulpkolom dan.
Ik begrijp er nu echt helemaal niets meer van.

Is er niet een kant en klare formule die ik kan kopiëren en plakken?

Criteria die mee moeten tellen:
Roosterochtend
Roostermiddag
Thuiswerken
Ja

Criteria die niet mee moeten tellen:
Ziek
Vakantie
Roostervrij
Bijzonder verlof

Bereik:
Maandag: E4:E26
Dinsdag: F4:F26
Woensdag: F4:F26
(T/m vrijdag).

Hou het draadje op het forum en liever geen afsplitsingen naar DM's

[ Voor 5% gewijzigd door Lustucru op 02-02-2018 11:36 ]

[Icephase]

Angermanagement schreef op vrijdag 2 februari 2018 @ 11:11:
[...]

Omdat ik meerdere categorieën wel wil laten zien, en ik op 1 of andere manier niet meerdere criteria in de AANTALLEN.ALS neer kan zetten.

[knip]

Bij AANTAL.ALS kan dat niet, maar AANTALLEN.ALS kan wel degelijk meerdere selectiecriteria aan. Hoe dat werkt staat heel netjes in de Help van die functie...
Het komt op mij over of je iets heel eenvoudigs heel erg ingewikkeld aan het maken bent, en je niet echt secuur aan het werk bent (dat laatste is wel een vereiste m.i. voor goede Excel-sheets).

[Lustucru]

Icephase schreef op vrijdag 2 februari 2018 @ 11:05:
O.M.G...

Waarom gebruik je niet gewoon de functie AANTALLEN.ALS voor de categorie die je WEL wilt zien?

Omdat aantallen.als optelt wat aan álle criteria voldoet.

Niet te moeilijk denken!

Idd. Simpel en leesbaar: =aantal.als(R;x)+aantal.als(R;y)+aantal.als(R;z)+....

[Icephase]

Lustucru schreef op vrijdag 2 februari 2018 @ 11:57:
[...]

Omdat aantallen.als optelt wat aan álle criteria voldoet.

Ja dat wil hij toch ook? Criteria die niet meedoen, laat je er uit.

[...]

Idd. Simpel en leesbaar: =aantal.als(R;x)+aantal.als(R;y)+aantal.als(R;z)+....

Onzin. AANTALLEN.ALS is exact dit!
Maar een draaitabel maakt het pas écht simpel en gebruiksvriendelijk

[Lustucru]

Icephase schreef op vrijdag 2 februari 2018 @ 12:47:
[...]


Ja dat wil hij toch ook? Criteria die niet meedoen, laat je er uit.


[...]


Onzin. AANTALLEN.ALS is exact dit!

Grote woorden maar je vergist je. Doe me een plezier en probeer het even uit.

The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.

Wat hij zoekt is een manier om cellen in één range te tellen die aan één van meerdere criteria voldoen.

parafraserend:
aantalAls(Range1=Criterium1 AND Range2=criterium2 AND Range3=Criterium3)
vs
aantalAls(range1=criterium1 OR range1=criterum2 OR Range1=criterium3)

[Angermanagement]

Lustucru schreef op vrijdag 2 februari 2018 @ 13:22:
[...]


Grote woorden maar je vergist je. Doe me een plezier en probeer het even uit.


[...]


Wat hij zoekt is een manier om cellen in één range te tellen die aan één van meerdere criteria voldoen.

parafraserend:
aantalAls(Range1=Criterium1 AND Range2=criterium2 AND Range3=Criterium3)
vs
aantalAls(range1=criterium1 OR range1=criterum2 OR Range1=criterium3)

Dit.

Roodey heeft inmiddels een mailtje verzonden, ik wacht even op antwoord.
Zodra het werkt post ik de oplossing hier en zal ik melden dat er een slotje op kan.

[Angermanagement]

Oplossing:

Aanwezig: =AANTALARG(E3:E25)-E27
Afwezig: ==AANTALLEN.ALS(E3:E25;"Roostervrij")+AANTALLEN.ALS(E3:E25;"Vrije dag")+AANTALLEN.ALS(E3:E25;"Training")+AANTALLEN.ALS(E3:E25;"Ziek") etc.

Met dank aan Roodey!