Formule vastzetten bij invoegen kolom

[Chris15]

Ik heb in A2 de formule =SOM(G2:I2) staan.
Als ik een kolom invoeg, net voor G2, veranderd de formule naar =SOM(H2:J2)
Dit is niet de bedoeling, de oude formule moet blijven staan.
Is hier een "eenvoudige" oplossing voor?

 

[ExcelAmateur]

Heb je dit al geprobeerd?
=SOM($G$2:$I$2)

 

[Chris15]

Ja, geprobeerd, maar werkt niet.

 

[Gijsbert1]

En dit:

=SOM(VERSCHUIVING(A2;0;6;1;3))

 

[Chris15]

Volgens mij gaat dit werken, ik heb alleen een verkeerde cel doorgegeven waar de formule staat...

De formule staat in B2

Dan moet jou hele formule aangepast worden?

 

[Gijsbert1]

Nee hoor. Deze formule kan in elke willekeurige cel gezet worden...

 

[Chris15]

Ik heb de formule veranderd naar =SOM(VERSCHUIVING(B2;0;6;1;3)) aangezien het resultaat in B2 moet komen, maar dit werkt niet...

=SOM(VERSCHUIVING(A2;0;6;1;3)) werkt wel

Kan je mij de formule geven waar B2 in staat?

Bedankt voor je oplossing! Nu moet ik natuurlijk alles gaan lezen over "VERSCHUIVING"

 

[Gijsbert1]

Je moet de formule niet aanpassen maar gewoon in B2 zetten.
Die verschuiving formule wil zeggen:
1 startpunt is A2
2 vanaf daar 0 cellen naar beneden
3 vanaf daar 6 opzij naar rechts
(nu zitten we in G2)
4 het bereik is vanaf G2 1 rij(en) naar beneden groot. (Dus blijft G2)
5 het bereik is vanaf G2 3 kolommen naar rechts breed. (Dus tot I2)

Ik hoop dat je het snapt!

Je kunt bijvoorbeeld ook A1 als uitgangspunt nemen in de formule:

=SOM(VERSCHUIVING([COLOR="#FF0000"][B]A1;1[/B][/COLOR];6;1;3))

 

[Gijsbert1]

En als je persee B2 wilt zien als uitgangspunt in de formule, dan wordt het:

=SOM(VERSCHUIVING([COLOR="#FF0000"][B]B2;0;5[/B][/COLOR];1;3))

 

[Chris15]

Klasse Gijs, dank je!

Heb jij trouwens een idee waarom =SOM($G$2:$I$2) niet werkt? Dat was namelijk de 1e oplossing die ik via Google vond...

 

[Gijsbert1]

Ha Chris,

Met deze methode maak je de verwijzing absoluut i.p.v. relatief.
Dit heeft effect bij het doorvoeren en/of kopiëren van een formule naar andere cellen!
Dit heeft geen effect op de cel zelf waar de formule staat.
https://support.office.com/nl-nl/article/Overzicht-van-formules-7abfda78-eff3-4cc6-b4a7-6350d512d2dc
Bij het invoegen van een kolom of rij verplaats je de verwijzing, echter niet de formule!
Met de functie "VERSCHUIVING" kun je dit makkelijk afvangen.

 

[Gijsbert1]

Andere (beter leesbare) methode:

=SOM(INDIRECT("G2:I2"))

Alleen bij het doortrekken/kopiëren van de formule blijven de verwijzingen wel absoluut.

 

[Chris15]

Dank je Gijsbert! Deze formule is inderdaad veel makkelijker voor mij.

Het probleem hierbij is inderdaad het "kopiëren en plakken" omdat ik veel cellen met deze formule moet hebben.

VERSCHUIVING en INDIRECT zijn de enige oplossingen?

 

[Gijsbert1]

Doormiddel van een Macro in VBA kan het ook.
De verschuiving methode is toch het beste denk ik.

 

[Chris15]

EA en Gijsbert, bedankt voor de hulp!

Ik zal deze post op opgelost zetten