Sum Every 4 Cells
The OFFSET function is a function that returns a value based on a specific range of rows and columns from a reference range or a reference cell.
The OFFSET function can enable us to do the following:
- It eliminates the need for entering the desired ranges manually
- It eliminates the need for a complex formula
The OFFSET function can be used to write a formula that can SUM a range of cells, for instance, the example below shows summation of cells in two columns and four rows. The BASIC FORMULA for this example for Cell P6 is given below:
The generic formula for this function is given below:
=SUM(OFFSET(REFERENCE, ROWS, COLUMN(S), HEIGHT, WIDTH))
This is the cell where you begin the summation process. It is the starting point for the OFFSET function. In the example below, the reference is CELL C6.
- ROWS AND COLUMNS
This is the number of rows or column that has been specified to move or sum from the starting point.
In the example below, the rows to sum are two and the columns are four.
This is the height in the number of rows based on the returned reference. For instance, in the example below, we have specified that the rows should be 0. This means that the reference automatically starts from Cell C5 and moves two rows down. Assuming the rows was specified as 1, the reference will start from the Cell C but it will move one step down from the original reference to Cell C7.
This is similar to the discussion on height above.
How to Sum Every 4 Cells
Insert the values as shown in figure 1.
Click on Cell P6. Type the string below into the fx bar.
Click on fx and you will see the function argument window pop up as shown in figure 2 below. Of course, it will be empty until you complete the argument with what is below.
Note- *4 in the formula signifies the 4 cells we want to sum. If you want to sum just 3 cells, put *3 and so on.
Click on OK and your result will be as figure 3 below.
The cells that have been SUMMED to give the result of 37 as seen above are cells in rows C6 to F6 and C7 to F7.
Do you want to try one out? To sum the next four cells in the same arrangement, click on Cell Q6. Replace C6 in the basic formula with G6. You should have the formula below and your result should be 31.