< Go Back

Sum every 3 cells

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:

=SUM(OFFSET($C6,0,(COLUMN()-COLUMN($P$6))*4, 2,4))

The generic formula for this function is given below:

=SUM(OFFSET(REFERENCE, ROWS, COLUMN(S), HEIGHT, WIDTH))

 

  • REFERENCE

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.

  • HEIGHT

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.

  • WIDTH

This is similar to the discussion on height above.

How to Sum Every 4 Cells

Step 1

Insert the values as shown in figure 1.

Step 2

Click on Cell P6. Type the string below into the fx bar.

=SUM(OFFSET(

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.

$C6,0,(COLUMN()-COLUMN($P$6))*4,2,4))

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.

Step 3

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.

=SUM(OFFSET($G6,0,(COLUMN()-COLUMN($Q$6))*4,2,4))

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar