question about SUM - how to sum each for elements

A

arek.krawczynski

Hi

I have a problem with function SUM.

I have column A with numbers, and column B where I want to sum
elements from column A by four numbers together. So I put into B:
SUM(A1:A4)
SUM(A5:A8)
SUM(A9:A12)
SUM(A13:A16)
etc...

Is there any chance, to do it automaticly? I tried with everything
what I know. If I simply mark this, and pull down, Excel make it:
SUM(A1:A4)
SUM(A2:A5)
SUM(A3:A6)
SUM(A4:A7)
etc...

I was tring with different adressation $ or without $. But I couldn't
get that result.

I will be happy for any suggestions.
Hamster
 
T

T. Valko

Try this...

Assume you want the results to appear starting in cell B1.

Enter this formula in B1 and copy down as needed:

=SUM(OFFSET(A$1,(ROWS(B$1:B1)-1)*4,,4))

B1 will be the sum of A1:A4
B2 will be the sum of A5:A8
B3 will be the sum of A9:A12
B4 will be the sum of A13:A16
etc
etc
 
J

Jarek Kujawa

another way (not so neat as Biff's unfortunately), in A1:

=SUM(INDIRECT("A"&ROW()+3*(ROW()-1)&":A"&ROW()+3*(ROW())))

then copy down

HIH
 
A

arek.krawczynski

Try this...

Assume you want the results to appear starting in cell B1.

Enter this formula in B1 and copy down as needed:

=SUM(OFFSET(A$1,(ROWS(B$1:B1)-1)*4,,4))

B1 will be the sum of A1:A4
B2 will be the sum of A5:A8
B3 will be the sum of A9:A12
B4 will be the sum of A13:A16
etc
etc

Excelent!

Thank You both, guys! Can you tell me, where can I find any
informations about this solutions? What should I put into google to
find some description, becouse I need to use something like this in
different functions also.

Hamster
 
J

Jarek Kujawa

welcome
I'd follow Biff's suggestions
cause I used sources mentioned by him when I was learning formulae...
;-)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top