Even numbers are divisible by two. When an even number is divided by two, the reminder returns zero. You can use the MOD and SUMPRODUCT functions to count the number of even numbers.
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 the Even Numbers in Column C?
=SUMPRODUCT(–(MOD(C2:C10,2)=0),C2:C10)
The result returns -$3,404. There are four even numbers in column C: C6($0), C7(-$2,604), C9(-$800) and C10($0).
Explanation:
- Step 1: MOD(C2:C10,2): To find the remainder when the numbers in C2:C10 are divided by two;
- Step 2: (MOD(C2: C10,2)=0): If the remainder equals zero, the number is an even number and the result returns TRUE; otherwise, it returns FALSE;
- Step 3: (–(MOD(C2: C10, 2)=0)): The double hyphen converts TRUE into one and FALSE into zero;
- Step 4: The SUMPRODUCT function returns the sum of the products of the new array and values in C2:C10.
- Results: 782.03×0 + 23×0 + 576.74×0 + (-163)× 0 + 0×1 + (-2,604)× 1 + 56.78×0 + (-800)× 1 + 0×1 = -3,404
| Amount | Step 1 | Step 2 | Step 3 |
|---|---|---|---|
| $782.03 | 0.03 | FALSE | 0 |
| $23.00 | 1 | FALSE | 0 |
| $576.74 | 0.74 | FALSE | 0 |
| -$163.00 | 1 | FALSE | 0 |
| $0.00 | 0 | TRUE | 1 |
| -$2,604.00 | 0 | TRUE | 1 |
| $56.78 | 0.78 | FALSE | 0 |
| -$800.00 | 0 | TRUE | 1 |
| $0.00 | 0 | TRUE | 1 |
You can add up the numbers in one column if the corresponding cell in another column is an even number.
Question: What is the Sum in Column B if the corresponding cells in Column C are Even Numbers?
=SUMPRODUCT(–(MOD(C2:C10,2)=0),B2:B10)
The result returns 1,800. There are four even numbers in column C, and the corresponding cells in column B are B6 (300), B7 (200), B9 (800), and B10 (500).
Explanation:
- Step 1: MOD(C2:C10,2): To find the remainder when the numbers in C2:C10 are divided by two;
- Step 2: (MOD(C2: C10,2)=0): If the remainder equals zero, the number is an even number and the result returns TRUE; otherwise, it returns FALSE;
- Step 3: (–(MOD(C2: C10, 2)=0)): The double hyphen converts TRUE into one and FALSE into zero;
- Step 4: The SUMPRODUCT function returns the sum of the products of the new array and values in B2:B10.
- Results: 1,278×0 + 800×0 + 588×0 + 300 × 0 + 300×1 + 200 × 1 + 300×0 + 800× 1 + 500×1 = 1,800
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.
