Excel - sommen.als maar dan met variabele kolom

[CeesKees]

Mijn vraag
LInks in de tabel heb ik een reeks letters (A-Z) en horizontaal de maanden. De letters kunnen meerdere keren voorkomen. Nu wil ik de som van de waarde die bij de letters hoort, voor een bepaalde maand.

Het lukt met sommen.als, maar die zit vast op een kolom. Ik wil dit variabel maken, door een bepaalde maand te selecteren en dan de waarde van die betreffende maand te zien.

Ik wil geen draaitabel gebruiken, omdat deze niet de opmaak kan geven die ik zoek.


Relevante software en hardware die ik gebruik
MS Excel

Wat ik al gevonden of geprobeerd heb
Sommen.als, maar dan "dynamisch" dat zoek ik

Ik heb hier een voorbeeldbestand:
https://we.tl/t-zQcoF7GhSe

[CeesKees]

Wat nog een optie kan zijn is een tussenblad, waarin ik het totaal van A-Z bereken, en dan de waarde elders naar voren laat komen met Verticaal zoeken

[Arjan90]

Ik snap eerlijk gezegd nog niet helemaal wat je wil doen. Kies je ergens in het bestand bijv. de "Maand" en "Letter"? Dan zou je op basis daarvan de formule wel kunnen maken. Met de formule "VERSCHUIVING" kun je bijv. bij kolom C:C beginnen (januari) en aan de hand van de maand bepalen hoeveel kolommen hij moet opschuiven. Kun je wat printscreens en formules laten zien? De meeste mensen hier zullen geen Excel bestanden van onbekenden openen op hun computer omdat dit risico's geeft.

[CeesKees]

Ik heb hier het voorbeeld..

Wat ik dus wil is in de dropdownbox (gele cel) de maand selecteren, en dan de som van A van de betreffende maand weergeven. Nu heb ik sommen.als, en zit deze "vast" op de april kolom

[ Voor 5% gewijzigd door CeesKees op 27-08-2020 17:18 ]

[chengbondkwok]

Ok @CeesKees als je onderstaande formule gebruikt, kan je het ook nog doortrekken:

=SUMIFS(INDEX($C$6:$O$30;;MATCH($U$6;$C$6:$O$6;0));$C$6:$C$30;$T7)

Moet je alleen zelf even de Nederlandse benaming erbij zoeken

In cel U7 plakken, en dan naar beneden doortrekken.

[ Voor 137% gewijzigd door chengbondkwok op 27-08-2020 17:36 ]

[CeesKees]

Is een optie, als ik alle waarden in de rij (C) maar 1x had. Dan moet ik deze eerst filteren met een tussentabel (ook nog een optie)

[coop]

Zou het anders werken met een tussenkolom? In kolom R doe je dan over de gehele lengte een formule die de waarde uit D-O haalt op basis van U6 met een HLOOKUP. Op basis van die kolom doe je dan je sommen.als in kolom U. Theoretisch zou je de opmaak van de cellen in R nog witte tekst kunnen geven dat je de waarde niet ziet, of gewoon een kolom verbergen?

[CeesKees]

Dat is ook nog een interessante workaround coop.

[coop]

code:

=SUMIFS(INDIRECT(ADDRESS(7;MATCH($U$6;$D$6:$O$6;0)+2) & ":" & ADDRESS(30;MATCH($U$6;$D$6:$O$6;0)+2));$C$7:$C$30;T7)

Zou het moeten doen voor het voorbeeld hierboven.

[Arjan90]

VERSCHUIVING is volgens mij echt je beste optie, dan kun je ook uit met een "gewone" SOM.ALS. Ik heb het al in 5 minuten even in elkaar geknutseld. Het belangrijkste is de berekening die je per letter maakt:

code:

=SOM.ALS(B:B; L4; VERSCHUIVING(C:C; 0; VERGELIJKEN($M$2;$C$2:$H$2;0)-1))

Dit is op basis van het volgende bestand, de formule staat dan dus in M4:



Uitgewerkt:
Met de SOM.ALS formule maak je een vergelijking, je zoekt dus allereerst in kolom B naar de waarde in cel L4 (dat is de "te rapporteren" letter). Vervolgens ga je een optelbereik bepalen. Daar wordt het wat lastiger, omdat je dan moet bepalen in welke kolom de maand staat. Hiervoor gebruik je VERSCHUIVING en VERGELIJKEN. Met VERSCHUIVING kun je een aantal rijen of kolommen laten verschuiven vanaf het bereik wat je kiest (in dit geval vanaf kolom C).

Dit kun je doen met een VERGELIJKEN formule, hij gaat dan zoeken naar wanneer een waarde in een bepaald bereik voorkomt. In dit geval zet je in de VERGELIJKEN formule dus als zoekwaarde de maand die je geselecteerd hebt en het zoekbereik is de tabelrij met maanden (begin bij "Jan" met zoeken). De uitkomst daarvan is positie 1 t/m de maximale positie. Daarom moet je altijd -1 doen, omdat hij anders één kolom teveel gaat verschuiven, het makkelijkste voorbeeld daarvoor is "Jan": dat is de eerst gevonden waarde, omdat uitkomst van VERGELIJKEN 1 t/m de maximale positie is, zal hij dus 1 als waarde teruggeven. Als je dan niet - 1 doet, betekent dit dat hij al een kolom opschuift (en dus effectief in februari gaat zoeken, in plaats van in januari).

Kom je zo verder?

[ Voor 58% gewijzigd door Arjan90 op 27-08-2020 17:59 ]

[Bolletje]

https://exceljet.net/form...criteria-multiple-columns

Exact wat je nodig hebt. Je moet een array-functie gebruiken.

[ Voor 16% gewijzigd door Bolletje op 27-08-2020 17:58 ]

[McMark]

Bolletje schreef op donderdag 27 augustus 2020 @ 17:58:
https://exceljet.net/form...criteria-multiple-columns

Exact wat je nodig hebt. Je moet een array-functie gebruiken.

Dit inderdaad! Let wel op dat je niet teveel criteria of teveel data toevoegt. Dan gaat excel zich snel verslikken met de gebruikte Formule

[Arjan90]

@Bolletje Dan heb je het bericht toch niet helemaal goed gelezen denk ik, jouw voorbeeld (met de array functie) telt alles van "Red" op, in plaats van ook rekening te houden met de maand. De waarde die hij weergeeft zal dus te hoog zijn. Er zit een extra variabele bij, daar wordt in de array formule geen rekening mee gehouden. Ik ben er echt van overtuigd dat mijn aangedragen oplossing de meest elegante oplossing is en gebruikt maakt van alle "standaard" beschikbare formules, zonder een hele zware formule te zijn.

[ Voor 22% gewijzigd door Arjan90 op 27-08-2020 18:01 ]

[Bolletje]

Jewel,

Here you go:

code:

=SUMPRODUCT(($C$7:$C$30=$T7)*($D$6:$O$6=$V$6)*($D$7:$O$30))

Het gaat om het concept.

[ Voor 65% gewijzigd door Bolletje op 27-08-2020 18:06 ]

[Arjan90]

Zo worden de oplossingen wel exact gegeven, ik betwijfel of TS daar echt mee geholpen is. Juist het begrip over hoe het werkt is veel belangrijker. Daar leer je van, zeker met Excel.

Ik weet dat het ook kan met een array formule, maar ik adviseer iedereen wel om van array formules af te blijven als je het op een andere nette manier kan oplossen. Zeker op grote schaal zijn array formules vertragend op je systeem, je ontkomt er niet altijd aan maar in veel gevallen kun je het mooier oplossen. Soms letterlijk door andere benaderingen met draaitabellen om op basis van waarden in te zoeken.

Het klinkt zo wel een beetje als een kruistocht voor mijn eigen oplossing, maar juist die oplossing maakt gebruik van formules die, als je ze goed weet te gebruiken, veel vaker gebruikt kunnen worden. De VERGELIJKEN functie kun je bijv. ook bij INDEX VERGELIJKEN (ter vervanging van het oude VERT.ZOEKEN) gebruiken. Hoewel er inmiddels ook een nieuwe VERT.ZOEKEN functie gebouwd is en wordt uitgerold.

[chengbondkwok]

Wat je ook nog kan doen is gebruik maken van de nieuwe

code:

xlookup

functionaliteit.

Dan krijg je de volgende formule:

code:

=SUMIFS(XLOOKUP($U$6;$D$6:$O$6;$D$7:$O$30);$C$7:$C$30;T7)

Wat je doet is nog steeds een SUMIFS functie, met als voordeel dat XLOOKUP alle kanten kan opkijken.

Je vult eerst de SUMIFS range in, en dat zoek je op met XLOOKUP. Je doet XLOOKUP waarbij je zoekt naar de waarde in U6, wat de maand is, en dat moet voorkomen in de kolommen tussen D6 en O6. Je wilt het getal hebben wat in D7 t/m O30 ligt.

Vervolgens pas je je eerste SUMIF criteria toe, namelijk dat het A moet zijn. Dus selecteer je C7 t/m C30, en zeg je dat je op zoek bent naar de waarde in T7.

Werkt overigens alleen als je Excel via Office 365 hebt.

[CeesKees]

TS gaat hiermee aan de slag en laat weten wat het gewenste resultaat geeft.

Dank jullie wel voor zover!

[Steve Schouten]

Hi all,
Ik heb een vergelijkbare uitdaging.
Het verschil alleen is dat ik in de casus van @CeesKees de som zou willen weten van bv Rapportageperiode A én B.
Kan dat in één handige formule?
Of @Arjan90 moet je dan 2x min of meer dezelfde formule optellen:
=SUMPRODUCT(($C$7:$C$30=$T7)*($D$6:$O$6=$V$6)*($D$7:$O$30)) +
SUMPRODUCT(($C$7:$C$30=$T8)*($D$6:$O$6=$V$6)*($D$7:$O$30))