Excel formule

[Std]

Beste HelpMij,

De waarden optellen van het betreffende jaar.
Als het jaartal in C1 gewijzigd is in een ander jaar, dat excel de waarde telt horend bij het betreffende jaar.

Als voorbeeld heb ik een excel bestand toegevoegd.

Wie kan èn wil mij helpen?

mvg

Std.

 

[Senso]

Ik zie drie datums. Allemaal in 2021? Ik begrijp het voorbeeld niet. Welke uitkomst in welke cel is gewenst?

 

[Std]

Dat klopt.
Als ik het jaartal 2021 (C1) wijzig in 2020 dat dan de waarden bij dat jaar opgeteld worden.

 

[AlexCEL]

Zie bijlage.

- Ik heb je waarden in C10 tm C21 veranderd in echte datums, maar wel de weergave gehouden zoals in je origineel. -
- In kolommen G en H gebruik ik een SOMMEN.ALS om de waarden op te tellen.
- Waardes in C25 t/m C27 zijn altijd groter dan 1 dus de ALS-formules in A25 t/m B27 zijn overbodig...

 

[Std]

Beste AlexCel,

dat werkt gedeeltelijk, maar als datum gewijzigd is in 2-1-21 dan telt Excel niets.....

 

[AlexCEL]

Inderdaad, over het hoofd gezien. Wijzig formule in G10 in:

=SOMMEN.ALS(G$25:G$27;$C$25:$C$27;">="&$C10;$C$25:$C$27;"<="&[COLOR="#FF0000"]LAATSTE.DAG($C10;0)[/COLOR])

En trek door naar rechts en onder.

 

[Std]

Zie bijlage.

- Ik heb je waarden in C10 tm C21 veranderd in echte datums, maar wel de weergave gehouden zoals in je origineel. -
- In kolommen G en H gebruik ik een SOMMEN.ALS om de waarden op te tellen.
- Waardes in C25 t/m C27 zijn altijd groter dan 1 dus de ALS-formules in A25 t/m B27 zijn overbodig...

Dat is wat ik bedoel maar dan heel graag met met behoud van opmaak cellen in het voorbeeld bestandje. Dus kolom C ongewijzigd houden.

 

[AlexCEL]

Waarom niet een formule? De opmaak is hetzelfde...

Maandnamen gebruiken gaat de formules complex maken. En om de vraag maar voor te wezen:

=SOMMEN.ALS(G$25:G$27;$C$25:$C$27;">="&DATUM($C$1;VERGELIJKEN($C10;{"jan";"feb";"mrt";"apr";"mei";"jun";"jul";"aug";"sep";"okt";"nov";"dec"};0);1);$C$25:$C$27;"<="&LAATSTE.DAG(DATUM($C$1;VERGELIJKEN($C10;{"jan";"feb";"mrt";"apr";"mei";"jun";"jul";"aug";"sep";"okt";"nov";"dec"};0);1);0))

 

[popipipo]

Of misschien op deze manier?

=SUMIFS(G$25:G$27;$C$25:$C$27;">="&[COLOR="#FF0000"]DATE($C$1;MONTH(L10&1);[/COLOR]1);$C$25:$C$27;"<="&[COLOR="#FF0000"]DATE($C$1;MONTH(L10&1)+1;0)[/COLOR])

 

[Std]

Waarom niet een formule? De opmaak is hetzelfde...

Maandnamen gebruiken gaat de formules complex maken. En om de vraag maar voor te wezen:

=SOMMEN.ALS(G$25:G$27;$C$25:$C$27;">="&DATUM($C$1;VERGELIJKEN($C10;{"jan";"feb";"mrt";"apr";"mei";"jun";"jul";"aug";"sep";"okt";"nov";"dec"};0);1);$C$25:$C$27;"<="&LAATSTE.DAG(DATUM($C$1;VERGELIJKEN($C10;{"jan";"feb";"mrt";"apr";"mei";"jun";"jul";"aug";"sep";"okt";"nov";"dec"};0);1);0))

Met de aangereikte formule werkt het wel en is het resultaat precies zoals bedoelt. Tja 't is wel een beetje heel erg complex. Nog 1 puntje, optellen tot en met vandaag. Alles in "de toekomst" achterwege laten.

 

[popipipo]

In mijn voorbeeld:

=SUMIFS(H$25:H$27;$C$25:$C$27;"<="&DATE($C$1;MONTH(L12&1)+1;0))

 

[AlexCEL]

Alles in "de toekomst" achterwege laten.

Dan maak ik dit van mijn formule:

=SOMMEN.ALS(G$25:G$27;[COLOR="#FF0000"]$C$25:$C$27;"<="&VANDAAG();[/COLOR]$C$25:$C$27;">="&DATUM($C$1;VERGELIJKEN($C10;{"jan";"feb";"mrt";"apr";"mei";"jun";"jul";"aug";"sep";"okt";"nov";"dec"};0);1);$C$25:$C$27;"<="&LAATSTE.DAG(DATUM($C$1;VERGELIJKEN($C10;{"jan";"feb";"mrt";"apr";"mei";"jun";"jul";"aug";"sep";"okt";"nov";"dec"};0);1);0))

Of voor Excel 365:

=LET(
x;DATUM($C$1;VERGELIJKEN($C12;{"jan";"feb";"mrt";"apr";"mei";"jun";"jul";"aug";"sep";"okt";"nov";"dec"};0);1);
y;LAATSTE.DAG(x;0);
z;VANDAAG();
SOMMEN.ALS(G$25:G$27;$C$25:$C$27;">="&x;$C$25:$C$27;"<="&y;$C$25:$C$27;"<="&z))

 

[Std]

Thnx AlexCEL! Met =SOMMEN.ALS(G$25:G$4444;$C$25:$C$4444;"<="&VANDAAG();$C$25:$C$4444;">="&DATUM($C$1;VERGELIJKEN($C10;{"jan";"feb";"mrt";"apr";"mei";"jun";"jul";"aug";"sep";"okt";"nov";"dec"};0);1);$C$25:$C$4444;"<="&LAATSTE.DAG(DATUM($C$1;VERGELIJKEN($C10;{"jan";"feb";"mrt";"apr";"mei";"jun";"jul";"aug";"sep";"okt";"nov";"dec"};0);1);0)) is het nu helemaal naar mijn zin! :thumb:

 

[Std]

Beste AlexCEL,

Is het ook mogelijk om op dezelfde manier het aantal transacties te tellen per maand?

=aantallen.als(25:G$4444;$C$25:$C$4444;"<="&VANDAAG();$C$25:$C$4444;">="&DATUM($C$1;VERGELIJKEN($C10; {"jan";"feb";"mrt";"apr";"mei";"jun";"jul";"aug";"sep";"okt";"nov";"dec"};0);1);$C$25:$C$4444;"<="&L AATSTE.DAG(DATUM($C$1;VERGELIJKEN($C10;{"jan";"feb";"mrt";"apr";"mei";"jun";"jul";"aug";"sep";"okt"; "nov";"dec"};0);1);0))

 

[AlexCEL]

Dat kan zeker:

=AANTALLEN.ALS(G$25:G$4444;">0";$C$25:$C$4444;"<="&VANDAAG();$C$25:$C$4444;">="&DATUM($C$1;VERGELIJKEN($C10;{"jan";"feb";"mrt";"apr";"mei";"jun";"jul";"aug";"sep";"okt";"nov";"dec"};0);1);$C$25:$C$4444;"<="&LAATSTE.DAG(DATUM($C$1;VERGELIJKEN($C10;{"jan";"feb";"mrt";"apr";"mei";"jun";"jul";"aug";"sep";"okt";"nov";"dec"};0);1);0))