Sum function that skips rows when dragged down

Hello all,

I have a program outputting data to an Excel file into a single column of 4 rows per measurement. I'd like to sum some of these and put the results in a summary table on the same sheet, but I only need the top two rows or the bottom two rows at a time for each measurement. For example:

_B8: 1a ...... _E8: =_B8+_B9 (1a+1b)
_B9: 1b ...... _E9: =B12+B13 (2a+2b)
B10: 1c ...... E10: =B16+B17 (3a+3b)
B11: 1d
B12: 2a
B13: 2b
B14: 2c
B15: 2d
B13: 3a
B14: 3b
B15: 3c
B16: 3d

This should go on for hundreds, maybe thousands of rows, so I'd like to get it to where I can just Ctrl-Enter the data table. How can this be done? I've been playing around with the OFFSET function, but I haven't quite figured it out.

Thanks.

Are the real data numbers? If so, does this work for you?

Formula:

=SUM(INDIRECT("b"&4+4*(ROW()-7)&":b"&5+4*(ROW()-7)))

in E8 and copy down

Assuming your data always starts in B8 try this

Formula:

=SUM(OFFSET($B$8:$B$9,(ROWS($1:1)-1)*4,))

Originally Posted by lmmorrison

Thanks for the quick replies! Both functions work great when starting at E8, but gak67's works in any other cell

Because gak67 made the sensible choice! See here for a discussion on the merits of using ROWS as opposed to ROW in this type of construction:

http://excelxor.com/2014/08/23/row-v...er-generation/

Regards

Originally Posted by XOR LX

Because gak67 made the sensible choice!

Cruel but fair... Go with GAK - he's already had some rep from me...

Originally Posted by Glenn Kennedy

Cruel but fair... Go with GAK - he's already had some rep from me...

Sorry, Glenn!

This ROW vs ROWS thing has become a bit of a thing for me! Nothing personal intended!

Explanation:
The SUM function is fairly easy, but it needs a range to sum. That's where the OFFSET function comes in.

With the OFFSET function, if the range to offset from is greater than 1 cell the function keeps that range size, so that's why I had $B$8:$B$9 as the reference. If that's not the first two cells you want to sum then change it as appropriate.

I then needed to tell Excel how many rows to offset. The ROWS function returns the number of rows in an array. The array I chose was one row high, but the first row was an absolute reference so as the formula is dragged or copied down the number of rows increases. I used $1:1 but could have used any single row array, such as $152:152, or even AB$20:BZ20, as long as it started as one row high and the first row reference was an absolute reference. The -1 is because you cannot have a range of 0 rows and to start with you need to offset 0 rows. If you had the starting reference as $B$4:$B$5 you wouldn't need the -1.

Because your data is in blocks of 4 rows I multiplied the number of rows by 4.

Hope that helps.

No problem. I'm here to learn...