Summation of every 3 (Quarterly) values of cells.

Hello Friends,

In this topic, I will tell you the trick/formula to the sum of every nth row and column of a range in excel.

If you know about the OFFSET function will easily understand this example. If you want to learn about the offset function, then follow the offset function.

In the above example, I have taken a monthly sales record of the items of a computer store. Now I want to make a report to find the quarterly sales of each item. To get the result I have used a formula.

=SUM(OFFSET($C6,0,(COLUMN()-COLUMN($P$6))*3, 1,3))

As we know, to calculate the first quarter, we have to add the first three months of sales. The sales data for the first three months is in the same row and 3 different columns. So in formula row is 0.

To add the value of 3 different months, mention the height (1) and Width (3). By changing the value of height and width you can add as many numbers of rows/columns as you want.

Similarly, you can get the result if your monthly data in rows wise by using the formula

=SUM(OFFSET(C$3,(ROW()-ROW($H$3))*3,0,3,1))

Offset Function in Excel

Hello Friends,

The "Offset" function in excel is a powerful function. The Offset function returns a reference to a range that is given a number of rows and columns from a given reference. It is a function that returns the value of cell or range of cells which is specified by a number of rows and columns from the cell and range of cells.

Syntax of the Offset Function.

=OFFSET (reference, rows, cols, [height], [width])

Reference: Reference of cell or range of cells from where a row and column count will start.

Rows: Count the number of rows below (a positive value), above (a negative value), same row (0).

Column: Count the number of column right (a positive value), left (a negative value).

Height: It is optional. The height in rows of the returned reference.

Width: It is optional. The Width in a column of the returned reference.


Example of the OFFSET function to understand it.

In this example, I have taken monthly sales of 3 items of a computer store. Now I want to calculate the sum of the sales of all three items in the month of July.

I used a formula

=SUM(OFFSET(A3,6,1),OFFSET(A3,6,2),OFFSET(A3,6,3))

In the above formula, I have used the OFFSET function 3 times for the references of three different items.
A3 is the starting point from where we can calculate the number of rows and columns. The reference of the "January"  value of  A3 is 0 row. and 0 columns.

OFFSET(A3,6,1) returns the reference of the cell which is located at 6 rows below and 1 column right from the cell A3.

It is a very powerful function and widely used in a complex function. Here some more examples of OFFSET function.


Sum Of every 3 cells