sommen.als met horizontale en verticale voorwaarden

[ARBjr]

Betreft: Excel 2016:

Ik probeer vanuit een tabel/matrix waarden op te halen, middels meerdere voorwaarden/criteria.

Ik denk dat dit niet lukt omdat ik mijn criteria zowel horizontaal als verticaal probeer op te halen.

Zie ook het voorbeeld bestand. Momenteel maak ik gebruik van de functie SOMMEN.ALS

De waarden (antwoord van de formule) dienen te komen in de cellen G8 t/m G60.
G8 dient gelijk te zijn aan cel W18
G17 dient gelijk te zijn aan cel AF18.

Wanneer de filters aangepast worden in E3 t/m E5 -> worden ook de criteria aangepast in C8 t/m E60.
Waardoor de antwoorden in kolom G (8 t/m 60) ook aangepast worden.

De formule welke ik nu gebruik in celG8 =SOMMEN.ALS($W$8:$BW$103;$W$7:$BW$7;$F8;$V$8:$V$103;$E8;$U$8:$U$103;$D8;$T$8:$T$103;$C8)

Ik hoop dat er hier een slimmerik is, die mij kan vertellen wat ik niet goed.

[Belindo]

Wellicht is een screenshot van hoe je data eruit ziet, plus een van wat je probeert te bereiken handig. Met alleen deze tekst als voorbeeld begrijp ik niet wat je precies probeert te doen.

[ARBjr]

@ Belindo, helemaal met je eens! Ik probeer het bestand te delen... maar dat lukt niet echt...
Zie hier onder een printscrren

[Renzzie]

Waarom verwacht je die waarden in g8 en G17? Zie daar de logica niet van.

[ Voor 53% gewijzigd door Renzzie op 13-10-2020 14:34 ]

[ARBjr]

heb het voorbeeld bestand online gezet
https://www.helpmij.nl/fo...entid=351658&d=1602592133

@ Renzzie... ik vat je opmerking niet helemaal.
in G8 dient de waarde te komen.... van filiaal STAD van JAAR 2020 van type OVER en van week 1
in G9 alles idem maar dan van week 2
in G10 wordt dan week 3 etc

[Renzzie]

Hierbij kan ik je helaas niet helpen. Persoonlijk zou ik de brondata anders neerzetten en vanuit hier gewoon een simpele draaitabel gebruiken.

Je zoekt naar een formule die verticaal en horizontaal zoekt op verschillende voorwaarden. Dit lijkt me sowieso een super foutgevoelige benadering, noch afgezien van het feit of je het werkend krijgt of niet.

[Leut]

Het is zoals @Renzzie zegt, de brondata verwerken in een draaitabel en dan middels de functie 'draaitabel.ophalen' de gegevens laten ophalen en verwerken. Dat werkt perfect in jouw voorbeeld file.

Pas dan ook gelijk de jaartallen in G7 en H7 aan, zodat deze verwijzen naar E3. Je krijgt dan zoiets als dit:

[ydderf]

Alles in één formule zetten zal wel lastig zijn. Ik zou zo ff niet weten hoe in ieder geval Ik zou het inderdaad eerder zoeken in de hierboven aangedragen oplossing met de draaitabel.
Een andere oplossing zou meer in de richting van een VBA script worden. Daarmee kun je ook door je data gebied heen "loop-en" en zoeken naar bepaalde waardes. Hiermee zou je uiteindelijk wel weer de juiste getallen moeten kunnen uitlezen (en eventueel optellen).
Maar van deze VBA oplossing is het instapniveau misschien wel iets hoger. Een ook voor de onderhoudbaarheid op langere termijn, zou ik eerder neigen naar de draaitabel oplossing.
Al vindt de hobby programmeur de VBA oplossing leuker

[Belindo]

Ah, ik was deze even vergeten gisteren @ARBjr.

Waar bij jou de bottleneck zit, is dat je niet weet wat je kolom voor de SUM is. Voor week 1 moet dit kolom W zijn, voor week 2 is het kolom X, etc.

Wat wél meewerkt is dat je weken oplopen van 1 t/m 53. Hierdoor kun je OFFSET gebruiken met als kolom offset je weeknummer.

Omdat we beginnen met week 1 in plaats van week 0, pak je de kolom links van de eerste week als je referentie. Je zit dan op kolom V. Met een kolom offset van (week) 1 kom je uit op kolom W. Met een kolom offset van (week) 2 kom je uit op kolom X, etc.

Dan is het alleen nog een kwestie van je offset formule te plaatsen op de plek van de sum range in de SUMIFS formule:

code:

=+SUMIFS(OFFSET(V:V,,F8),V:V,$E8,U:U,$D8,T:T,$C8)

Disclaimer: ik heb voor de 'IFS' kolommen V, U en T even de hele kolom gepakt in plaats van de range 8 t/m 103. Hierdoor blijft de formule ook werken als je nieuwe data onderaan je tabel plakt (verder dan rij 103)

[ Voor 8% gewijzigd door Belindo op 14-10-2020 13:09 ]