You can use the SUMPRODUCT and MOD functions to have the sum of the numbers in the even rows.
Example: You are working with a worksheet with the product in column A, the amount in column B, and the profit/loss in column C.
Question: What is the sum of numbers in even rows in column B?
=SUMPRODUCT(MOD(ROW(B2:B10)+1,2), B2:B10)
The result returns 2,966.
Explanation:
- Step 1: ROW(B2:B10): To have the row number;
- Step 2: MOD(ROW(B2:B10)+1,2): To have the remainder when the row number adds one and divides two. The remainder returns zero when the even row number divides two. To keep the even rows, we need to add one, so the remainder returns one when it divides two.
- Step 3: The SUMPRODUCT function returns the sum of the products of the new array and the range B2:B10.
You can use the following formula to have the sum of the numbers in column C in the even rows.
=SUMPRODUCT(MOD(ROW(B2:B10)+1,2), C2:C10)
The result returns 1,415.55.
Notes: The SUMPRODUCT Function
The SUMPRODUCT function adds all the multiplication results for all arrays.
Formula:
=SUMPRODUCT(array1, [array2], …)
Explanations:
– Array1 is required; the first array is to multiply and add.
– Array2 is optional; the second array is to multiply and add.