Excel Sommen als met Horizontaal en verticaal zoeken

[wbberends]

Hallo
Ik heb onderstaande niet werkende formule opgesteld, ik ben op zoek naar een oplossing:

=SOMMEN.ALS(Projecten!L2:AZ5000;Projecten!$K$4:$K$5000;2021;Projecten!L1:AZ1;"Piet")
Hierbij geldt:
Databereik is L2:AZ5000
Criterium 1 is dat ik Verticaal wil zoeken naar projecten in 2021
Criterium 2 is dat ik Horizontaal wil zoeken naar een bepaald persoon


Onderstaande formule met enkel criterium 1 werkt wel:
=SOM.ALS(Projecten!$K$2:$K$5000;$B21;Projecten!L$2:L$5000)

Het toevoegen van criterium 2 lukt me helaas niet

Ik hoor graag wat de oplossing is

[g0tanks]

SOMMEN.ALS is niet gemaakt om zowel verticale als horizontale criteria te gebruiken. Een omweg is om SOMPRODUCT te gebruiken:


Ik weet alleen niet hoe goed dit schaalt. Met 5000 regels kan het wel eens heel langzaam worden. Als je googelt op 'sumif with vertical and horizontal criteria' kun je vast efficiëntere oplossingen vinden.

[Patrick_6369]

In de meeste datasets ga ik er van uit dat het resultaat van iets dergelijks altijd maar één cel is? Is dat in jouw geval ook zo? Want dan kun je goed INDEX en MATCH combineren. Die werken redelijk licht.

[Lustucru]

Begrijp ik goed dat je de bedragen uit 2021 wilt optellen in de kolom ‘Piet’?

In dat geval gebruik je som.als met criterium 2021 voor de kolom met jaartallen en het optelbereik bepaal je met verschuiving() of met indirect()

In het voorbeeld van @g0tanks bereken je de bedragen over 2020 voor piet als volgt:

code:

=SOM.ALS(A2:A6;2020;VERSCHUIVING(A2:A6;0;VERGELIJKEN("Piet";A1:E1)))

[ Voor 26% gewijzigd door Lustucru op 06-10-2021 17:07 ]

[DCV191283]

wbberends schreef op woensdag 6 oktober 2021 @ 13:38:
=SOMMEN.ALS(Projecten!L2:AZ5000;Projecten!$K$4:$K$5000;2021;Projecten!L1:AZ1;"Piet")
Hierbij geldt:
Databereik is L2:AZ5000
Criterium 1 is dat ik Verticaal wil zoeken naar projecten in 2021
Criterium 2 is dat ik Horizontaal wil zoeken naar een bepaald persoon

Als ik dit lees krijg ik het idee dat het om een forse hoeveelheid regels aan data gaat? Mijn keuze zou in dat geval een draaitabel zijn, dan kan je eruit krijgen wat je wil.

[wbberends]

Dank allemaal voor de tips en feedback!

Met jullie reacties heb ik het werkend gekregen!

[Lustucru]

g0tanks schreef op woensdag 6 oktober 2021 @ 14:25:
SOMMEN.ALS is niet gemaakt om zowel verticale als horizontale criteria te gebruiken. Een omweg is om SOMPRODUCT te gebruiken:

De TS is er mee geholpen maar het is wel echt lelijk... Je doet een optelling van een complete matrix waarbij je eerst 1 kolom isoleert door de rest (dwz 99% in het geval van de TS) van de matrix met nul vermenigvuldigt. Daarna gebruik je een formule die bedoeld is om het resultaat van de vermenigvuldiging van twee of meer matrices te sommeren om de elementen van één matrix bij elkaar op te tellen. Doe dan gewoon {=SUM(matrix)}.

Niet jouw schuld (je schrijft duidelijk het is een omweg en er zijn betere oplossingen denkbaar) maar het is nu wel het beste antwoord geworden...