May 24 2021 11:53 AM
Hello,
I am struggling with a summation problem. I have the following three cells:
C1 =SUM(Q3:Q38)
C2 =SUM(Q40:Q75)
C3=SUM(Q77:Q112)
(These have been entered manually. However, I have the do this for 900 summations in the end)
The patterns is thus that every time 37 is added start and ending cell of the summation 'series'. (I am skipping 1 value in between every series).
What I want is thus to continue this pattern, what would obviously result in the following
C4 =SUM(Q114:Q149)
C5 =SUM(Q151:Q186)
....
However, when I 'drag down' the formula for all the values in the row, excel does not recognize the pattern I want.
Thanks in advance.
May 24 2021 12:09 PM
SolutionMay 24 2021 12:25 PM
@Hans Vogelaar Bedankt Hans, helemaal super! Is het te veel gevraagd om nog even de 'logica' achter de formule uit te leggen zodat ik hem in het vervolg makkelijk zelf weet te gebruiken?
May 24 2021 12:38 PM
ROW() geeft het rijnummer van de cel met de formule terug, Dus in C1 krijg je 1, in C2 krijg je 2, enz.
37*ROW()-34 geeft 3 in C1, 40 in C2 enz.
INDEX(Q:Q,37*ROW()-34) is dan cel Q3, Q40 enz.
Net zo geeft 37*ROW()+1 38 in C1, 75 in C2 enz.
Dus INDEX(Q:Q.37*ROW()+1) verwijst naar cel Q38, Q75 enz.
In C1 is de formule =SUM(INDEX(Q:Q,37*ROW()-34):INDEX(Q:Q,37*ROW()+1)) gelijkwaardig met =SUM(Q3:Q38). In C2 met =SUM(Q40:Q75), enz.
May 24 2021 12:09 PM
SolutionIn C1:
=SUM(INDEX(Q:Q,37*ROW()-34):INDEX(Q:Q,37*ROW()+1))
Fill or copy down.