[Excel 2016] Bereik in Sumproduct

[DHH]

In Excel 2016 probeer ik d.m.v. SUMPRODUCT een bereik op te halen aan de hand van een aantal criteria.

Dat lukt aardig, echter wil ik in dat bereik ook rekenen met de maanden (welke simpelweg als getal 1 t/m 12 zijn opgegeven).

Als ik als criteria de exacte maand opgeef bijv. voor maart ="3", dan komt de juiste waarde naar voren. Als ik alle waarden vóór maart wil pakken (<"3"), worden januari + februari meegenomen, maar ook november en december.

Uiteindelijk wil ik dit gebruiken om uit de tabel de totale 'year to date' te berekenen o.b.v. < MONTH(TODAY(), maar zelfs zonder formulereferenties lijkt dat al niet te lukken.

De getallen in C1 t/m N1 zijn ook als type 'getal' gedefinieerd, dus dat zou goed moeten zijn.
Ik heb combinaties geprobeerd met "numbervalue=3" in de formule, maar dit nog niet werkende gekregen.



Ik ben erg benieuwd naar hoe het wel moet.

[Lustucru]

DHH schreef op woensdag 27 februari 2019 @ 16:46:
[...]
Als ik als criteria de exacte maand opgeef bijv. voor maart ="3", dan komt de juiste waarde naar voren. Als ik alle waarden vóór maart wil pakken (<"3"), worden januari + februari meegenomen, maar ook november en december.
[...]

De getallen in C1 t/m N1 zijn ook als type 'getal' gedefinieerd, dus dat zou goed moeten zijn.

Als het getallen zijn is het '<3' en niet '<"3"'. Met die aanhalingstekens dwing je een tekstvergelijking af. En idd: "11" is kleiner "3", net zoals "aa" kleiner is dan "c". Oktober wordt ook meegenomen btw.

[DHH]

Klopt, alleen de vergelijking heeft aanhalingstekens nodig. Zonder aanhalingstekens werkt de functie niet.

Overigens had ik verwacht dat (voor tekstvergelijking) "10" ook kleiner zou zijn dan "3", maar deze wordt niet meegenomen.

[Lustucru]

DHH schreef op woensdag 27 februari 2019 @ 17:26:
Overigens had ik verwacht dat (voor tekstvergelijking) "10" ook kleiner zou zijn dan "3", maar deze wordt niet meegenomen.

Want 11+11+6+6=44?

[DHH]

Lustucru schreef op woensdag 27 februari 2019 @ 17:58:
[...]

Want 11+11+6+6=44?

Touché

[Sparhawk]

Maanden als letters nummeren?

Januari= a
etc...

[dix-neuf]

Als Country=A1:A5, Productgroup=B1:B5, #Headers=A1:N1, kun je dit gebruiken:

code:

Voor "3": 
=index(Table2;match("BE";if(Table2[Productgroup]="A";Table2[Country];0));match("3";Table2[#Headers];0))

Voor "<3": 
=index(Table2;match("BE";if(Table2[Productgroup]="A";Table2[Country];0));match("1";Table2[#Headers];0))+index(Table2;match("BE";if(Table2[Productgroup]="A";Table2[Country];0));match("2";Table2[#Headers];0))

Formules invoeren via Ctrl-Shift-Enter, vergeet dat niet !

[ Voor 6% gewijzigd door dix-neuf op 27-02-2019 21:26 ]

[DHH]

Klinkt als een betere oplossing. Ik ga 'm morgen even proberen!
Thanks!


Dank voor de suggesties. Het antwoord van dix-neuf werkt, maar zou betekenen dat voor bijv. oktober 9x
match("x";Table2[#Headers];0) moet invullen (waar x = 1 t/m 9). Dat is toch niet echt praktisch.

Uiteraard was het ook mogelijk om de maanden A, B, C, etc. te noemen, maar dat is me m'n eer te na. ;-)

Uiteindelijk blijkt voor mij de oplossing:
=SUMPRODUCT((A2:A5=C22)*(B2:B5=C23)*(C1:N1<C25),C2:N5)

Dit werkt echter niet als de gegevensset als tabel is aangemerkt. Om een of andere reden worden de maandnummers dan niet langer als nummer herkend. Vreemd

Iedereen bedankt voor het meedenken!

[ Voor 81% gewijzigd door DHH op 28-02-2019 09:51 ]

[Lustucru]

DHH schreef op woensdag 27 februari 2019 @ 16:46:

De getallen in C1 t/m N1 zijn ook als type 'getal' gedefinieerd, dus dat zou goed moeten zijn.

Dat zijn ze dus niet. Kijk maar naar de uitlijning. Op het moment dat je er een tabel van maakte is e.e.a. omgezet in kop_tekst_.

Je maakt ook apart gebruik van de som.product() functie. Die is bedoeld om verschillende matrices met elkaar te vermenigvuldigen en het resultaat op te tellen, maar je voert de functie één zelf samengestelde matrix. Dan kun je idd beter direct je eigen matrixfunctie gebruiken die met als() conditie's de juiste matrix opbouwt.

[dix-neuf]

DHH schreef op woensdag 27 februari 2019 @ 21:16:
Het antwoord van dix-neuf werkt, maar zou betekenen dat voor bijv. oktober 9x
match("x";Table2[#Headers];0) moet invullen (waar x = 1 t/m 9).

Dat is niet nodig, daarvoor kun je offset gebruiken.
Voor maand 1 t/m 9 bv., invoeren via Ctrl-Shift-Enter:

code:

=SUM(OFFSET(INDEX(Table2;MATCH("BE";IF(Productgroup="A";Country;0));MATCH("1";Table2[#HEADERS];0));;;;9))

En voor de maanden < 3 (jan. + febr):

code:

=SUM(OFFSET(INDEX(Table2;MATCH("BE";IF(Productgroup="A";Country;0));MATCH("1";Table2[#HEADERS];0));;;;2))

[ Voor 20% gewijzigd door dix-neuf op 28-02-2019 15:15 ]

[DHH]

Dank voor de verduidelijking, beiden!

[Lustucru]

DHH schreef op woensdag 27 februari 2019 @ 16:46:
Ik ben erg benieuwd naar hoe het wel moet.

Het antwoord van @dix-neuf werkt ook, maar imho zijn de functies somproduct en productmat hiervoor bedoeld / het meest geschikt.

Met productmat bouw je een matrix op van cellen die voldoen aan de ingestelde criteria, en met somproduct vermenigvuldig je dat met je waardenmatrix en tel je het resultaat op. Op een eenduidige manier ben je vrijwel onbeperkt in het instellen van criteria van cellen die je wilt optellen.

code:

=SOMPRODUCT(PRODUCTMAT(N(RijCriterium) [*N(RijCriterium1..n)];N(KolomCriterium) [*N(KolomCriterium1..n));WaardenBereik)

bv:

code:

=SOMPRODUCT(PRODUCTMAT(N(A2:A5="a")*N(B2:B5="e");N(WAARDE(C1:E1)<3));C2:E5)

van binnen naar buiten:
1: N(A2:A5="a")*N(B2:B5="e") levert vier rijen op: 1 als kolom A=a en kolom B =e, anders 0;
2: N(WAARDE(C1:E1)<3) levert drie kolommen op 1 als de tekst een cijfer kleiner dan 3 presenteert, anders 0
3: PRODUCTMAT(¹;²) levert een matrix op van 4x3, 1 als aan beide voorwaarden is voldaan, anders 0;
4; SOMPRODUCT(³;C2:E5) vermenigvuldigt beide matrices en telt het hele zwikje bij elkaar op.

[ Voor 31% gewijzigd door Lustucru op 01-03-2019 11:42 ]

[DHH]

Inmiddels mijn rapportages af met de reeds gebruikte formules, maar de verduidelijking is wel prettig voor andere rapporten die ongetwijfeld gaan volgen.