Horizontaal zoeken en verticaal optellen

[Tobi79]

Beste Excel liefhebbers,
veel aan Tweakers gehad, maar zit nu met een uitdaging die ik zo 1-2-3 niet getackled krijg:

ik heb een datablad, waarop recepten staan. Iedere kolom (ingrediënt; G:AJ) is gekoppeld aan een grootboekrekening. iedere rij (=receptregel) beginnen met een segmenten-nummer (en komen meerdere keren voor), de rijen wil ik graag optellen in het output tabblad:

Het output tabblad bevat de journaalpost:
Grootboekrekening; kostenplaats; hoeveelheid.

Via het grootboekrekeningnummer moet Excel de kolom bepalen, de regels in die kolom moeten opgeteld worden, mits ze voldoen aan het segment(nummer).


Heb eerst gekeken of ik via een unieke sleutel kon maken via Grootboek x Segment, maar kreeg het niet voor mekaar hierop op het datablad te zoeken en ook op te tellen (met 119 regels en 36 kolommen).
Ook heb ik som.als geprobeerd, maar weet het komomnummer (vanaf A gerekend) niet te vertalen naar een letter.


Ik wil dus horizontaal zoeken en verticaal optellen? Wie is veel slimmer dan mij en kan me helpen?
Bij voorbaat dank!

[g0tanks]

Welkom op Tweakers. We hebben liever dat je een nieuw topic aanmaakt als er een nieuw probleem is, dus ik heb je reactie in Hoe kan ik horizontaal en verticaal zoeken en optellen? afgesplitst.

Kan je screenshots plaatsen van je sheets? Ik zie het op basis van je huidige beschrijving nog niet echt voor me.

[Tobi79]

Helder (weet ik dat voor een volgende keer).

in TabData staat het segment nummer in kolom A en de grootboekrekening in Regel 1

in TabJP:
B: de gele cellen het grootboekrekening (die verwijst naar de kolommen uit TabData, desbetreffende kolomnummer in kolom A) daarboven (4101) de tegenrekening die is niet relevant voor deze vraag.
C: het segment nummer, waarvan de daarbij behorende waarden opgeteld mogen worden.
E: De complexe formule die uit de matrix uit TabData de data optelt als grootboek en segment gelijk is aan (gb en segment van) de journaalpost regel.

[Tobi79]

Op TabData (2e plaatje) komen segmenten meerdere keren voor, Grootboek is nu nog uniek, maar dit kan in de toekomst veranderen. Ten gunste van het voorbeeld heb ik de afbeeldingen kort en bondig gehouden. Een journaalpost bevat 6 regels, 5 uitsplitsingen naar segmenten en 1 totaal van de grondstof, ik kan dit handmatig aanpassen voor de verschillende regels, als iemand me met de eerste regel kan helpen?

[Oon]

Ik snap nog niet helemaal wat je bedoelt.. Wil je per grootboekrekening de rijen optellen (dus bijv. grootboekrekening 3004, tel kolom G op, grootboekrekening 3011, tel kolom J op)?

[Tobi79]

Hoi Oon bedankt voor je hulp. ik zal hieronder proberen het te verduidelijken (anders hoor ik het graag):

op regel 2 (van het 1e plaatje) wil ik de opgetelde waarden uit de kolom (van het 2e plaatje) die correspondeert met Gb 3001 (de hulpkolom 1e plaatje geeft aan dat dat rij 7 vanaf A:A bezien betreft). Alleen opgeteld als het segment 300 (kolom A:A 2e plaatje) is.

Op regel 3 wil ik de opgetelde waarden uit de kolom die correspondeert met Gb 3001, als het segment 400.

Op regel 8 wil ik de opgetelde waarden uit de kolom die correspondeert met Gb 3002 (hulpkolom: kolom 23), als het segment 300 is.

[ Voor 34% gewijzigd door Tobi79 op 30-11-2020 15:52 ]

[hihans]

Ik begrijp hem nog niet helemaal, maar kijk eens in hoeverre je de functie sommen.als eng:sumifs hiervoor kan gebruiken.

[g0tanks]

hihans schreef op maandag 30 november 2020 @ 18:04:
Ik begrijp hem nog niet helemaal, maar kijk eens in hoeverre je de functie sommen.als eng:sumifs hiervoor kan gebruiken.

Met alleen SUMIF kom je er niet. De kolom waarin je de som wil pakken verandert namelijk afhankelijk van het grootboeknummer.

[g0tanks]

Hier een oplossing met SUMIF, INDEX en MATCH. De crux is om INDEX te gebruiken zonder een rijnummer op te geven. Hierdoor krijg je alle waarden in die kolom terug die als input gebruikt kan worden voor SUMIF.

[Lustucru]

qua leesbaarheid zou ik eerder gaan voor som.als i.c.m. verschuiving en vergelijken, al komt dat verder op hetzelfde neer.

=SOM.ALS(F4:f11;B4;VERSCHUIVING(F4:F11;0;VERGELIJKEN(a4;g3:j3)))

[Tobi79]

Brilliant!!
Net de index-formule leren kennen (wist niet dat je ook een ruimte [rij aanduiding] open kon laten) en Vergelijken is ook zo'n weinig gebruikte maar waardevolle formule.

Hartstikke bedankt!

Mocht iemand 'm in de toekomst in het NL nodig hebben:

=ALS.FOUT(SOM.ALS($F$4:$F$11;B4;INDEX($G$3:$J$11;;VERGELIJKEN(A4;$G$3:J$3;0)));0)