question about SUM - how to sum each for elements

[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. 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

 

[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

 

[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

 

[T. Valko]

You're welcome!

As far as where to look for help on formulas...

It's hard to be very specific since just about every formula is unique for
its application.

Here are a couple of websites that have a lot of info on various
functions/formulas:

http://www.cpearson.com/Excel/MainPage.aspx

http://contextures.com/

 

[Jarek Kujawa]

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