Formule met ALS.Sommen?

[Houten1948]

Aan alle kenners van Excel,
Vandaag voor het eerst aangemeld op deze site.
Ben met Excel een echte beginner.Bekijk bijlage Formule maand berekening.xlsx

Ik heb de volgende vraag:
Wie kan mij helpen aan een "eenvoudige" formule (geen VBA) die de kosten van een jaar per maand kan boeken?
Zie bijgevoegde sheet.

 

[jkpieterse]

Met een draaitabel waarbij de datum is gegroepeerd op maand en jaar:

 

[Houten1948]

Beste meneer Pieterse,

Met dank voor uw snelle reactie
Is het ook mogelijk zonder draaitabel maar met een formule, die kan ik beter lezen?
Bij de draaitabel komen alleen de te betalen bedragen in zicht als er een saldo is.
Is er geen saldo ingevuld dan in de tabel 0,00 invullen.
Met een "gewone" formule wordt de tabel "maandoverzicht" automatisch gevuld.
Als dat niet kan dan doe ik het met de draaitabel.
Vriendelijke groet
G. v. Burg

 

[jkpieterse]

Voordeel van een draaitabel is dat je rapport nooit onderwerpen mist en dat je er juist geen formules voor nodig hebt, die vaak van het trage soort zijn .

 

[sylvester-ponte]

Houten, welkom op helpmij.
je combineert vaste terugkerende kosten met variabele (benzine) kosten.
dat maakt het onnodig ingewikkeld.

voeg aub een kolom Startmaand en een kolom Interval toe. ipv een verhaaltje . excel rekent makkerlijker met getallen dan met verhaaltjes.
interval in maanden.
bvb:
elke maand geeft een interval van 1,
elke 3 maanden geeft een interval van 3 enz

 

[Houten1948]

Beste Sylvester,
Dank voor de tip.
Opmerking: In een boekhoudprogramma worden crediteuren door elkaar op een hoop gegooid.
De ene factuur moet ééns per jaar, de andere elk kwartaal of ééns per maand.
Maar als bij een bedrijf de monteurs moeten tanken dan gebeurt dat bijvoorbeeld elke twee dagen.
Het gaat erom dat de kosten voor elke maand zichtbaar zijn per kostenpost.
Overigens is dit excel programma niet voor een bedrijf.
Neem een voedselbank, die heeft elke maand op verschillende wijze kosten.
vriendelijke groet
G. von Burg

 

[Houten1948]

Beste meneer Pieterse,
Uw redenering is juist. Het nadeel van een draaitabel is dat je heb telkens moet vernieuwen.
Het nadeel van een "gewone" formule is dat het systeem trager reageert.
Dat laatste neem ik voor lief als de formule goed werkt.
Dus het liefst probeer ik het nog met een formule, alleen weet ik niet hoe ik die moet samenstellen.
Ik heb de onderstaande formule geprobeerd, maar die werkt niet in mijn systeem en ik begrijp de formule niet.
=-SOMMEN.ALS(L_TRX_BEDRAGEX;L_TRX_CAT;$AF47;L_TRX_MAAND;K$4)

Ik houd mij aanbevolen.
vriendelijke groet
G. von Burg

 

[emields]

Als je dan toch met formules wil werken doe het dan zo.

 

[Houten1948]

"emields
Ik ben er mee bezig maar ik weet niet wat de matrix in het geheel doet.
Om privacy reden heb ik in het voorbeeld de rekening nummers en de benamingen zelf verzonnen
in werkelijkheid heb ik ca. 140 regels "crediteuren" en 40 grootboeknummers.
De draaitabel kan ik ook wel gebruiken als een extra controle
vriendelijke groet
G. von Burg

 

[emields]

dit is een voorbeeld waar je enkel nog uw betalingen moet invullen en de rest doet excel. (wel uw macro's inschakelen.)

 

[roeljongman]

met de volgende sommen.als formule zou het moeten lukken.

=SOMMEN.ALS(Betaaltermijnen!$G$5:$G$19;Betaaltermijnen!$D$5:$D$19;Maandoverzicht!$B8;Betaaltermijnen!$F$5:$F$19;">="&Maandoverzicht!D$7;Betaaltermijnen!$F$5:$F$19;"<"&ZELFDE.DAG(Maandoverzicht!D$7;1))

bij een sommen.als is het belangrijk dat voor alle bereiken de begin regel (5) en eindregel (19) gelijk zijn anders krijg je geen resultaat
de formule is zo opgebouwd dat je alle maanden kunt laten berekenen door kopieren van de formule naar de kolommen en regels zonder de bereiken of criteria aan te passen.

ik heb nu alleen de actieve rijen gebruikt uit het voorbeeld in een jaarbestand moet je 19 vermoedelijk vervangen door een hoge waarde (bijv rij 1000) zodat je altijd alle transacties meeneemt.

 

[sylvester-ponte]

Houten, is het de bedoeling dat alle maanden vooruit ingevuld worden ?
dus niet alleen het lijstje van betalingen dat al hebt ingevuld, maar voor het hele jaar?


misschien bedoel je zo iets: (ik heb de tabellen voor het controleren op de eerste pagina gezet)
en wat hulpkolommen geplaatst (het kan ook wel zonder hulpkolommen, maar dan worden de formules nog langer)

als het bvb door prijsveranderingen niet meer werkt dan wordt vba een stuk handiger.

 

[Houten1948]

Sylvester, Emields, roeljongmanter, info: De facturen komen over het hele jaar op willekeurige datums binnen net als bij een bedrijf. Die worden dan ingeboekt en dan wordt het bedrag geboekt op het juiste grootboeknummer in de juiste maand.
Zo heb je naast een jaaroverzicht (jaarrekening) en de kosten en opbrengsten (crediteuren en debiteuren) per maand de kosten overzichtelijk.

Met de huidige informatie aan formules ben ik er nu in geslaagd om dat deel goed aan te passen.
Het was al een hele toer om de formule in mijn eigen tabblad goed te krijgen maar dat is nu gelukt.
Ik wil iedereen hartelijk danken voor de input, het heeft mij meer inzicht gegeven hoe (voor mij) moeilijke formules werken.
Indien nodig doe ik graag nog een beroep op jullie.
vriendelijke groet
G. von Burg

 

[emields]

Wegens ons allen graag gedaan en vergeet niet in uw eerste post de vraag als opgelost te zetten.

 

[jkpieterse]

Of zo, middels dynamische matrix formules (moet je wel Office 365 hebben).

 

[cow18]

mooi, jkp ! Misschien nog een hulpkolom in de tabel met tekst van "jj-mm" om de som-formules ietsje te vereenvoudigen

 

[Houten1948]

Ik heb office 2019, dynamische matrix formules werken dus niet.
(Ik weet ook niet wat dynamische matrix formules zij, zover rijkt mijn kennis niet)
moet bij mij vooral eenvoudig zijn met begrijpbare formules.
GvB

 

[jkpieterse]

Dat is jammer, dan kan je mijn oplossing negeren.