SOMMEN.ALS - optellen wanneer een waarde in een kolom voldoet aan reeks waarden

[TonSlagboom]

Ik probeer het onderstaande te bereiken:

1. In sheet A staan grootboektotalen per jaar (kolommen: grootboeknummer, grootboeknaam, jaar en totaal). Alleen deze sheet mag de gebruiker straks verversen.
2. In sheet B staan koppelingen van een grootboeknummer naar een door mij bedachte groepering (Mappings). Deze sheet gaat voor de gebruiker onzichtbaar worden.
3. In sheet C probeer ik dynamisch totalen te tonen via SOMMEN.ALS die de koppeling moet leggen naar een groepsnaam + jaar in Sheet C ---> Sheet B (Groepnaam -> grootboeknummer) --> Sheet A (totaal van de gevonden grootboeknummers binnen de gezochte groepsnaam). Maar dat krijg ik niet voor elkaar. Hier moet ik combinaties gaan maken met SOMMEN.ALS en ALS / VERT.ZOEKEN.

Wie kan mij daarbij helpen ?
Het voorbeeld heb ik versimpelt in het ingesloten Excel-document. Daar staat alles in 1 sheet.

Hierin wil ik proberen D17 en D18 dynamisch te laten bepalen aan de hand van C17 en C18.
Of D27 + D28 te bepalen aan de hand van een komma-gescheiden reeks van grootboeknummers in kolom B27/B28.

Thanks by voorbaat !

 

[E v R]

in D17 de volgende matrixformule:

=SUM(IF($B$3:$B$11=IFERROR(MATCH($B$3:$B$11&"_"&$D$3:$D$11;IF(tbl_Mapping[Reporting Group]=[COLOR="#FF0000"]$C17[/COLOR];tbl_Mapping[Ledgernr]&"_"&$C$14;"");0);0);$E$3:$E$11;0))

 

[TonSlagboom]

Dank Erik, maar ik krijg als resultaat #Naam. Zou je dat in mijn Excel-document willen zetten en uploaden. Dan kan ik het bestuderen.

 

[SjonR]

Bekijk bijlage Excel Sample using Mapping Table.xlsx

Ik heb de Nederlandse versie:

=SOM(ALS($B$3:$B$11=ALS.FOUT(VERGELIJKEN($B$3:$B$11&"_"&$D$3:$D$11;ALS(tbl_Mapping[Reporting Group]=$C17;tbl_Mapping[Ledgernr]&"_"&$C$14;"");0);0);$E$3:$E$11;0))

Deze werkt bij mij wel, maar aangezien het een matrixformule betreft, zal je na het invoeren van de code moeten afsluiten met CTRL+SHFT +ENTER.

Mooie formule trouwens Eric.

 

[E v R]

Thanks Sjon

Overigens wanneer er gebruik gemaakt wordt van Excel 2013 of hoger: gebruik dan niet de 'draak-functiie' als.fout maar als.nb (hoewel het in deze formule nagenoeg geen pijn doet)

 

[TonSlagboom]

Dat was inderdaad de oplossing (ctrl-shift-enter)
Vinden jullie het erg dat ik een vreugde-sprongetje heb gemaakt !

Dank mannen !

 

[SjonR]

Alle credits voor Eric! En dat soort sprongetjes maak ik geregeld dankzij dit forum.

 

[E v R]

Als overigens je grootboeknummers nog wijzigen, werkt het nog niet :d

wellicht zo wel:

=SOMPRODUCT($E$3:$E$11*(als.fout(vergelijken($B$3:$B$11&"_"&$D$3:$D$11;IF(tbl_Mapping[Reporting Group]=$C$17;tbl_Mapping[Ledgernr]&"_"&$C$14;"");0);0)>0))

of

=SOM((als.fout(vergelijken($B$3:$B$11&"_"&$D$3:$D$11;IF(tbl_Mapping[Reporting Group]=$C$17;tbl_Mapping[Ledgernr]&"_"&$C$14;"");0);0)>0)*$E$3:$E$11)

wel afsluiten met CSE

 

[TonSlagboom]

Aanpassing

Inderdaad kunnen de grootboekrekeningen wijzigen, daarom probeer ik jouw 2 andere voorstellen toe te passen maar dat lukt me niet. Ook na CSE niet .
In de bijlage een nieuw voorbeeldbestand. Daarin heb ik inmiddels extra voorwaarden toegevoegd en gegevens in verschillende sheets gezet.

 

[E v R]

zie bestandje

 

[TonSlagboom]

Fantastico, dit werkt !
Dank je Eric.