There are times when the data in our sheet has been organized in such a way that we need to add the values located in alternate rows.
Unfortunately there is no function, or command, that does this type of calculation and therefore it will be necessary to create our own formula that will help us add the values in alternate rows.
For our example, we will assume that we are interested in adding the alternate rows of the data shown in the following image:
There are at least two methods that will help us do this type of addition and we will review them below.
Auxiliary column to distinguish rows
The first method we have to add the alternate rows involves creating an auxiliary column that helps us distinguish the alternate rows.
That helper column will help us distinguish between odd and even rows so that we can add only some of them.
We will use the ROW function to obtain the number of the row in which we are, and to that result we will apply the RESIDUAL function to know if we are in an odd or even row.
The formula that I will place in cell I2 will be as follows:
The second argument of the WASTE function indicates that we will obtain the remainder of the division by two.
In this way, if the row is even, the value returned by the RESIDUAL function will be zero, on the contrary, if the row is odd, the RESIDUAL function will return the value one.
When copying this formula in all the cells of the auxiliary column we will obtain the following result:
Formula for adding alternate rows
Now that we have distinguished the odd and even rows in our data, we can use the SUMIF function to sum those elements that are in an odd or even row.
The ADDIF function allows us to evaluate a condition before including a value in the sum, and for our first formula, we will evaluate the Auxiliary column to know if it has the value 0 and if this condition is met we will add the value of the column of the corresponding month.
For example, to add the Budget rows (which are in blue) for the month of January, I will use the following formula:
The first argument of the function is the range of the auxiliary column, which we will use as criteria to choose the values to be added. The second argument is the criteria that the cells in the auxiliary column must meet to be considered in the sum.
The cells of the Auxiliary column will not be added, but the SUMARIF function allows us to indicate as its third argument the range of cells that contains the values that will be added.
In our example, cells in the range C2: C13 will be summed as long as their value in the Auxiliary column is equal to zero. Entering this formula in cell C15 we get the following result:
The number 44085 is the sum of the values in the January column found in the even rows of the sheet (blue rows). If we wanted to do the sum of the values in the odd rows (yellow rows) we could modify our formula in the following way:
The only difference with the previous formula is that the second argument of the SUMIF function is the number 1 that asks the function to include only those rows whose value in the auxiliary column is equal to one. Using this formula in cell C16 we get the following result:
Lastly, I’ll copy the formulas in cells C15 and C16 to the right to get the totals for the following months.
In this way we have managed to sum the values in alternate rows in Excel using an auxiliary column and the SUMIF function.
In a strict sense, the method shown is not limited to adding alternate rows, but you could create different types of criteria to ask the function to add only those values that meet the established condition.
Matrix formula to sum alternate rows
The second method we can use to add alternate rows is with an array formula. It is important to mention that this method is recommended only for users who are already familiar with the use of matrix formulas.
With this method we will avoid the use of the Auxiliary column since, due to the nature of matrix formulas, we will be able to include this calculation within the same formula.
Our first objective is to obtain a matrix with numbers 0 and 1 so that the number 1 is located in those rows that must be considered in the sum.
The following array formula helps us to generate an array of numbers 1 for the even rows on the sheet.
The matrix returned by this formula will be used with the SUMPRODUCT function to make the cross with the values of the month of January so that, when multiplying each one of them, only the values multiplied by 1 remain.
I will enter the above formula in cell C15 remembering that array formulas are entered with the Ctrl + Shift + Enter key combination. The result is as follows:
To make the sum of the odd rows, we only modify a little the formula that generates the matrix of numbers 1 as follows:
The difference with the previous formula is that we do not add 1 to the result of the ROW function and in this way we will have an array of numbers 1 for the odd rows. When doing the combination with the function SUMARPRODUCTO we will have the following formula:
I will enter the above formula in cell C16 and we will have the following result:
You can copy the formulas to the right and compare them with the results of the method in the Auxiliary column and you will see that they are the same.
The two methods that we review this time will help us solve the problem of adding alternate rows. The first method is easier for novice users to implement.
In contrast, the second method requires you to be familiar with array formulas in Excel as you could continually get errors by forgetting to enter them correctly.