0

So I have an excel file over some years with 10min values [A2;A181441].

I want to SUM every row of 144 values into [C2;C1261].

Looking forward to your answers!

edit: Thanks @Rajesh S for update: Row of 144 values not every 144th value.

  • Did you mean to say that you want to Sum every 144 row in huge range? – Rajesh S Aug 10 at 10:23
  • Yes a row of 144, not 144th – Bfzn Aug 10 at 10:24
  • you mean bunch of 144 rows! – Rajesh S Aug 10 at 10:25
  • ,, do you have values in group like City of Region,, like Col A has City name (same city from 1 to 144) and Column B has number to SUM,, or better share some sample data with us. – Rajesh S Aug 10 at 10:40
  • Col A is Date in 10min: 09.07.2015 00:00, 09.07.2015 00:10, and so on. Col B is Energy in kWh. – Bfzn Aug 10 at 10:47
2

This will solve the issue:

enter image description here

N.B.

  • For better understanding in the shown example I've got the SUM of 5 rows (or bunch of 5 rows).
  • Formula in Cell E98:

    =SUM(OFFSET($B$98,(ROW()-98)*5,0,5,1))

  • Your Formula should be written like this.

=SUM(OFFSET($B$98,(ROW()-98)*144,0,144,1))

  • If your data are in Range A1:B1000 along with headers in Row 1 then use this Formula in Cell D2.

=SUM(OFFSET($B$2,(ROW()-2)*144,0,144,1))

Adjust cell references in the formula as needed.

0

Here is an alternative that uses Index() rather than Offset():

=SUM(INDEX(A$2:A$181411,1+144*(ROWS($1:1)-1)):INDEX(A$2:A$181411,ROWS($1:1)*144))

and copy downward.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.