Hello,






I have a large table of more than 2000 rows, and 5 columns. The values of these cells are either 1 or 0. For each column I want to be able to count the number of 1s in blocks of 20 rows starting from the first row. Example: For each column count the number of 1s in the first 20 cells (A1:A20), then in cells 21 to 41 (A21:A41), then from 42 to 62 and so on. Any help is appreciated.


Many thanks,
egit

Are you sure this is blocks of 20? The range A21:A41 has 21 cells, as does A42:A62
I will assume all blocks are 20 cells and you want the data compiled in a column starting in the first row.

Put a formula in cell H1 with this value
=SUM(OFFSET($A$1,(ROW()-1)*20,0,20,1))

Then copy this formula down for as many rows as you need.

__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

An option to avoid the OFFSET volatile function

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Many thanks, both solutions work.

Please mark thread as solved ( see Thread tools). Thx

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post