SOLVED

SUM problem.

%3CLINGO-SUB%20id%3D%22lingo-sub-2381625%22%20slang%3D%22en-US%22%3ESUM%20problem.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2381625%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20struggling%20with%20a%20summation%20problem.%20I%20have%20the%20following%20three%20cells%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EC1%20%3DSUM(Q3%3AQ38)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EC2%20%3DSUM(Q40%3AQ75)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EC3%3DSUM(Q77%3AQ112)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3E(These%20have%20been%20entered%20manually.%20However%2C%20I%20have%20the%20do%20this%20for%20900%20summations%20in%20the%20end)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EThe%20patterns%20is%20thus%20that%20every%20time%2037%20is%20added%20start%20and%20ending%20cell%20of%20the%20summation%20'series'.%26nbsp%3B%20(I%20am%20skipping%201%20value%20in%20between%20every%20series).%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EWhat%20I%20want%20is%20thus%20to%20continue%20this%20pattern%2C%20what%20would%20obviously%20result%20in%20the%20following%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EC4%20%3DSUM(Q114%3AQ149)%3CBR%20%2F%3EC5%20%3DSUM(Q151%3AQ186)%3CBR%20%2F%3E....%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EHowever%2C%20when%20I%20'drag%20down'%20the%20formula%20for%20all%20the%20values%20in%20the%20row%2C%20excel%20does%20not%20recognize%20the%20pattern%20I%20want.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2381625%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2381710%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20problem.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2381710%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061966%22%20target%3D%22_blank%22%3E%40Hugofranke%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20C1%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUM(INDEX(Q%3AQ%2C37*ROW()-34)%3AINDEX(Q%3AQ%2C37*ROW()%2B1))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20or%20copy%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2381769%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20problem.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2381769%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20Bedankt%20Hans%2C%20helemaal%20super!%20Is%20het%20te%20veel%20gevraagd%20om%20nog%20even%20de%20'logica'%20achter%20de%20formule%20uit%20te%20leggen%20zodat%20ik%20hem%20in%20het%20vervolg%20makkelijk%20zelf%20weet%20te%20gebruiken%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2381844%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20problem.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2381844%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061966%22%20target%3D%22_blank%22%3E%40Hugofranke%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EROW()%20geeft%20het%20rijnummer%20van%20de%20cel%20met%20de%20formule%20terug%2C%20Dus%20in%20C1%20krijg%20je%201%2C%20in%20C2%20krijg%20je%202%2C%20enz.%3C%2FP%3E%0A%3CP%3E37*ROW()-34%20geeft%203%20in%20C1%2C%2040%20in%20C2%20enz.%3C%2FP%3E%0A%3CP%3EINDEX(Q%3AQ%2C37*ROW()-34)%20is%20dan%20cel%20Q3%2C%20Q40%20enz.%3C%2FP%3E%0A%3CP%3ENet%20zo%20geeft%2037*ROW()%2B1%2038%20in%20C1%2C%2075%20in%20C2%20enz.%3C%2FP%3E%0A%3CP%3EDus%20INDEX(Q%3AQ.37*ROW()%2B1)%20verwijst%20naar%20cel%20Q38%2C%20Q75%20enz.%3C%2FP%3E%0A%3CP%3EIn%20C1%20is%20de%20formule%20%3DSUM(INDEX(Q%3AQ%2C37*ROW()-34)%3AINDEX(Q%3AQ%2C37*ROW()%2B1))%20gelijkwaardig%20met%20%3DSUM(Q3%3AQ38).%20In%20C2%20met%20%3DSUM(Q40%3AQ75)%2C%20enz.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2381860%22%20slang%3D%22en-US%22%3ERe%3A%20SUM%20problem.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2381860%22%20slang%3D%22en-US%22%3EHans%2C%20je%20bent%20een%20held!%3C%2FLINGO-BODY%3E
New Contributor

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. 

 

 

 

 

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

@Hugofranke 

In C1:

 

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

 

Fill or copy down.

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

@Hugofranke 

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.

Hans, je bent een held!