Excel -> Meerdere criteria in zelfde criteriabereik?

[Anoniem: 1057697]

Beste Tweakers,

Zeker ben ik niet of ik dit in de juiste categorie heb geplaatst, desalniettemin heb ik een vraag waar ik al een redelijke tijd mee stoei.

Excel is prachtig, maar ik krijg het niet voor elkaar om via een formule meerdere criteria aan te geven in eenzelfde criteria bereik. Ik zal dit nader uitleggen.

Op dit moment gebruik ik een formule die hier op lijkt:

Voorbeeld 1
=SOMMEN.ALS(mutaties[bedrag];mutaties[categorie];"Secundaire uitgaven")+SOMMEN.ALS(mutaties[bedrag];mutaties[categorie];"Incidentele uitgaven")

Terwijl ik hem liever op deze manier zou willen zien:

Voorbeeld 2
=SOMMEN.ALS(mutaties[bedrag];mutaties[categorie];"Secundaire uitgaven","Incidentele uitgaven")

Mijn vraag is: Is er daadwerkelijk een oplossing die er voor kan zorgen dat ik bijvoorbeeld in eenzelfde formule, zonder een hele formule met veel zoekopdrachten te moeten herhalen, naar meerdere criteria kan zoeken in eenzelfde criteria bereik zoals ik hier heb laten zien in Voorbeeld 2?

Mocht dit niet mogelijk zijn, dan heb ik pech en ga ik maar omslachtig te werk. Mocht dit wel mogelijk zijn, dan hoor ik dit uiteraard graag! Ik hoop dat iemand mij kan helpen en een makkelijkere oplossing heeft.

Alvast bedankt!

[Japidoff]

nou, als ik google op sommen.als, kom ik nagenoeg hetzelfde tegen als jouw voorbeeld 1....

SOMMEN.ALS(optelbereik;criteriumbereik1;criterium1;[criteriumbereik2; criterium2];...)

n beetje zoiets dus:

=SOMMEN.ALS(lijstje;mutaties[bedrag];"Secundaire uitgaven";mutaties[categorie];"Incidentele uitgaven")

gevalletje rtfm lijkt me,

F1 kan een hoop doen he?

[ Voor 68% gewijzigd door Japidoff op 30-03-2018 17:55 . Reden: typo ]

[Anoniem: 1057697]

Als je ook even goed mijn tekst leest, gebruik ik op dit moment voorbeeld 1. Maar ik zoek naar een oplossing zoals voorbeeld 2. Hetzelfde criterium bereik meerdere keren herhalen in dezelfde formule zorgt niet voor een correcte resultaat. Dit zorgt altijd voor resultaat 0. Hij telt op een een of andere manier niet de daadwerkelijke bedragen die ik nodig heb via die weg bij elkaar op. Dus iets minder nonchalant is ook prima als je niet eens inhoudelijk antwoord geeft op mijn daadwerkelijke vraag. In ieder geval bedankt voor het nemen van de moeite om via Google een oplossing te zoeken, want dat heb ikzelf natuurlijk nog niet gedaan

[Japidoff]

aaaah!

my bad, nu zie ik t jah

moet kunnen zou ik zeggen, maar speel eens met die 2 teksten, zet er eens een OR tussen ( ^ ) ipv een comma?

of kan je de voorwaarden is een aparte kolom zetten waar je dan naar verwijst?

post eens een stukje table, ik wil wel ff testen...

[Sethro]

Door array formule van te maken kun je een formule nesten in een som, dus zoiets als Som(als(x=y;als(x=z);0))

http://lmgtfy.com/?q=array+formula+sum+multiple+criteria

[Icephase]

Japidoff schreef op vrijdag 30 maart 2018 @ 19:56:
aaaah!

my bad, nu zie ik t jah

moet kunnen zou ik zeggen, maar speel eens met die 2 teksten, zet er eens een OR tussen ( ^ ) ipv een comma?

of kan je de voorwaarden is een aparte kolom zetten waar je dan naar verwijst?

post eens een stukje table, ik wil wel ff testen...

Sethro schreef op vrijdag 30 maart 2018 @ 20:06:
Door array formule van te maken kun je een formule nesten in een som, dus zoiets als Som(als(x=y;als(x=z);0))

http://lmgtfy.com/?q=array+formula+sum+multiple+criteria

Allemachtig jongens, het is Excel geen programmacode!

SOMMEN.ALS is voor wanneer je een soort EN-selectie doet voor meerdere criteria. Een OF-selectie kan die functie niet. Dan moet je met SOM.ALS()+SOM.ALS()+... enz gaan werken. Of je maakt een hulptabelletje.

Uiteraard zullen er slimmeriken zijn die zeggen dat je met OF() ook het eea kunt bereiken maar dat lijkt mij voor TS nou net niet handig. Die formules worden ook niet echt kort en zeker niet overzichtelijker.

[Anoniem: 1057697]

Sethro schreef op vrijdag 30 maart 2018 @ 20:06:
Door array formule van te maken kun je een formule nesten in een som, dus zoiets als Som(als(x=y;als(x=z);0))

http://lmgtfy.com/?q=array+formula+sum+multiple+criteria

Daarmee kun je geen heel bereik als criterium gebruiken. Ik gebruik verwijzingen naar kolommen van tabellen. Hier kom ik dus niet erg veel verder mee.

Voor de mensen die google blijven doorzoeken naar een oplossing
I searched the whole god damn internet. Vandaar dat dit forum mijn laatste hoop is.

[breew]

Anoniem: 1057697 schreef op zaterdag 31 maart 2018 @ 18:44:
[...]

I searched the whole god damn internet. Vandaar dat dit forum mijn laatste hoop is.

met vba een custom functie bouwen die precies doet wat jij wilt is niet zo heel erg ingewikkeld...
http://www.gratiscursus.b...eren/ExcelAut_les017.html

[Anoniem: 1057697]

Een eigen functie creëren in VBA heb ik geprobeerd. Het probleem daaraan is, is dat ik geen tabel verwijzingen kan gebruiken in VBA op de manier zoals ik ze gebruik in de worksheets zelf. Ook is het haast niet mogelijk om een LOOP te creëren uit een variabel waarin meerdere criteria staan die aangeduid zijn met een scheidingsteken. Een oplossing zou zijn dat via een LOOP of WHILE een variabel iedere keer uitkomst van een ander criteria er bij op te tellen, maar daarin loop je vast dat je niet meer de functie SOMMEN.ALS kunt gebruiken zoals je hem gebruikt, door de tabel verwijzingen. Ik verwijs niet naar directe cellen, maar naar kolommen van tabellen. Worksheet functions kun je terugkeren in VBA, maar niet als je tabelverwijzingen gebruikt. Als daar een oplossing voor is, dan hoor ik hem natuurlijk graag!

[breew]

Anoniem: 1057697 schreef op zondag 1 april 2018 @ 21:17:
Een eigen functie creëren in VBA heb ik geprobeerd. Het probleem daaraan is, is dat ik geen tabel verwijzingen kan gebruiken in VBA op de manier zoals ik ze gebruik in de worksheets zelf. Ook is het haast niet mogelijk om een LOOP te creëren uit een variabel waarin meerdere criteria staan die aangeduid zijn met een scheidingsteken. Een oplossing zou zijn dat via een LOOP of WHILE een variabel iedere keer uitkomst van een ander criteria er bij op te tellen, maar daarin loop je vast dat je niet meer de functie SOMMEN.ALS kunt gebruiken zoals je hem gebruikt, door de tabel verwijzingen. Ik verwijs niet naar directe cellen, maar naar kolommen van tabellen. Worksheet functions kun je terugkeren in VBA, maar niet als je tabelverwijzingen gebruikt. Als daar een oplossing voor is, dan hoor ik hem natuurlijk graag!

zoiets?

In dit voorbeeld moet je alle Ranges handmatig selecteren. Dat kun je natuurlijk naar wens automatiseren als je bereiken altijd op min-of-meer dezelfde plekken staan.

Voor het gemak van het testen even als Sub, maar vrij vlot om te bouwen naar een Function lijkt me..

Visual Basic:

Option Explicit
'=====================================================================
Sub SommenAlsMaarDanAnders()

  Dim rngBedrag As Range          'bereik met bedragen
  Dim rngCategorie As Range       'bereik met categorieën
  Dim rngSelectie As Range        'bereik met selectie waarop je sommen.als los wilt laten
  Dim arrSelectie() As Variant    'bereik voor waarden uit rngSelectie
  Dim i As Long: i = 1            'lusteller + initialisatie
  Dim cel As Range                'cel bereik
  Dim rngOutput As Range           'bovenste cel voor output
    
  'instellen van de bereiken
  Set rngBedrag = SelecteerBereik("bedragen")
  Set rngCategorie = SelecteerBereik("categorieën")
  Set rngSelectie = SelecteerWaardenUitBereik(rngCategorie)
  rngSelectie.Select
  'vullen van de array
  ReDim arrSelectie(1 To rngSelectie.Count)
  For Each cel In rngSelectie
    arrSelectie(i) = cel.Value
    i = i + 1
  Next cel
  
  'kies de locatie waar je de output wilt hebben
  Set rngOutput = Application.InputBox("Selecteer bovenste cel voor output", "Selecteer een geldig bereik", Type:=8)
  
  'schrijf uitkomsten wel naar de locatie voor output
  For i = 1 To rngSelectie.Count
    rngOutput.Offset(i - 1, 0).Value = arrSelectie(i)
    rngOutput.Offset(i - 1, 1).Value = Application.WorksheetFunction.SumIfs(rngBedrag, rngCategorie, arrSelectie(i))
    'hier worden de categorieën even los weggeschreven.. maar dit kun je uiteraard ook sommeren voor een totaal...
    'sommeer dan binnen de for-loop, en schrijf onder de regel met 'Next i' het totaal in één keer weg
  Next i
  
End Sub
'=====================================================================
Private Function SelecteerBereik(ByVal str As String) As Range
   
    Set SelecteerBereik = Application.InputBox("Selecteer bereik met " & str, "Selecteer een geldig bereik", Type:=8)

End Function
'=====================================================================
Private Function SelecteerWaardenUitBereik(ByVal rng As Range) As Range

  Dim cel As Range
  
  'bereik moet binnen rngCateorie vallen, dus doe een Intersect en
  'herhaal net zo lang (met een lelijke GoTo) tot alle cellen een geldige Intersect hebben
opnieuw:
  Set SelecteerWaardenUitBereik = Application.InputBox("Selecteer categorie(ën) voor SOMMEN.ALS", "Selecteer binnen het bereik met categorieën", Type:=8)
  For Each cel In SelecteerWaardenUitBereik
    If Application.Intersect(rng, cel) Is Nothing Then
      SelecteerWaardenUitBereik.Select
      MsgBox ("De geselecteerde cellen vallen niet in het opgegeven bereik met categorieën")
      GoTo opnieuw
    End If
  Next cel

End Function

[ Voor 10% gewijzigd door breew op 02-04-2018 13:05 ]

[Triggy]

Met @Sethro, Je moet een array formule gebruiken.

Invullen en ctrl-shift-enter doen:
=SOM(SOMMEN.ALS(mutaties[bedrag];mutaties[categorie];{"Secundaire uitgaven";"Incidentele uitgaven"}))

[ Voor 3% gewijzigd door Triggy op 02-04-2018 16:08 ]

[Sethro]

In excel zijn er meestal meerdere oplossingen, vba en arrays zijn al genoemd.

Andere optie is hulpkolom toevoegen aan de brontabel,in de kolom zet je dan formule neer die bepaalt of deze rij voldoet aan een van de criteria door te zoeken in de lijst met criteria. Dit kunnen er dan veel zijn. Dit kan door volgende formule te gebruiken: iserror(vlookup(waarde in tabel; lijst met criteria;false)).

Deze formule geeft dan true/false weer. Middels sumif formule kun je alle waardes die aan criteria voldoen optellen.

[Lustucru]

Triggy schreef op maandag 2 april 2018 @ 16:06:
Met @Sethro, Je moet een array formule gebruiken.

Invullen en ctrl-shift-enter doen:
=SOM(SOMMEN.ALS(mutaties[bedrag];mutaties[categorie];{"Secundaire uitgaven";"Incidentele uitgaven"}))

Dat is voor het gevraagde idd de oplossing, maar een beetje uitleg kan geen kwaad:

Som.als (sommen.als is overkill, omdat je maar één criteriumbereik hebt) accepteert een range of een matrixconstante als criteria. Som.als() retourneert dan een matrix met waarden; voor ieder criterium één.

Die tel je weer op met SOM (en dan moet je hem invoeren met ctrl-shift-enter) of je telt hem op met somproduct. Stel je hebt je criteriumwaarden in A1 en A2 staan dan zijn de volgende 4 varianten gelijk:

code:

=SOM(SOM.ALS([criteriumbereik];A1:A2;[OptelBereik]))
=SOM(SOM.ALS([criteriumbereik];{"Secundaire uitgaven";"Incidentele uitgaven"};[OptelBereik]))


=SOMPRODUCT(SOM.ALS([criteriumbereik];A1:A2;[OptelBereik]))
=SOMPRODUCT(SOM.ALS([criteriumbereik];{"Secundaire uitgaven";"Incidentele uitgaven"};[OptelBereik]))

Maar voor twee mogelijke waarden voor de criteria is Som.als()+som.als() nog wel de simpelste oplossing. Komen er meer mogelijke waarden dan ga je sneller naar de som(som.als()) variant.

[ Voor 25% gewijzigd door Lustucru op 08-11-2018 20:37 . Reden: werkt ook zonder invoer als matrixformule ]

[Anoniem: 662203]

*knip* respectloos

[ Voor 94% gewijzigd door Lustucru op 08-11-2018 20:22 ]