SOLVED

New Contributor

# SUM problem.

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.

4 Replies
best response confirmed by Hugofranke (New Contributor)
Solution

# Re: SUM problem.

In C1:

=SUM(INDEX(Q:Q,37*ROW()-34):INDEX(Q:Q,37*ROW()+1))

Fill or copy down.

# Re: SUM problem.

@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?

# Re: SUM problem.

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.

# Re: SUM problem.

Hans, je bent een held!