How to SUM number of consecutive rows or columns in EXCEL

 We have the data for the stocks of Apples and Mangoes at a grocery store.

We want to get a total for all Apples and Total for all mangoes in the store.

Here since the data is small, we can use SUM ( ) and get the answer but in a large data set, we can get this result quickly using OFFSET formula along with SUM

OFFSET helps to identify location and number of cells to add and then SUM does the addition.

Taking E5 as a reference to start locating rows we need for our calculation, our first row to start calculating from is E6. Hence in OFFSET formula, we add E5 as reference cell and then 1st row as start point. Since we are not considering to add any columns, we would add 0 for the column and then we need 4 rows from our reference row, hence would add 4 for the height in the OFFSET formula to locate our data of 4 rows.

=OFFSET(E5,1,0,4)

And then using SUM (OFFSET(E5,1,0,4)) we can get total for this 4 rows.

It is very common to use this formula while calculating weekly, monthly, quarterly or yearly totals or averages. It is also popularly used in finding running totals.

Using the same method, we can get totals in columns also.