Odd numbers are not divisible by two (e.g., one, three, five, etc.). When an odd number is divided by two, the remainder is one. You can use the MOD and SUMPRODUCT functions to count the number of odd 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 Odd Numbers in Column C?
=SUMPRODUCT(–(MOD(C2:C10,2)=1),C2:C10)
The result returns -$140. There are two odd numbers in column C: C3($23) and C5(-$163).
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)=1): If the remainder equals one, the number is an odd number and the result returns TRUE; otherwise, it returns FALSE;
- Step 3: (–(MOD(C2: C10, 2)=1)): 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×1 + 576.74×0 + (-163)× 1 + 0×0 + (-2,604)× 0 + 56.78×0 + (-800)× 0 + 0×0 = -140
| Amount | Step 1 | Step 2 | Step 3 |
|---|---|---|---|
| $782.03 | 0.03 | FALSE | 0 |
| $23.00 | 1 | TRUE | 1 |
| $576.74 | 0.74 | FALSE | 0 |
| -$163.00 | 1 | TRUE | 1 |
| $0.00 | 0 | FALSE | 0 |
| -$2,604.00 | 0 | FALSE | 0 |
| $56.78 | 0.78 | FALSE | 0 |
| -$800.00 | 0 | FALSE | 0 |
| $0.00 | 0 | FALSE | 0 |
You can add up the numbers in one column if the corresponding cell in another column is an odd number.
Question: What is the Sum in Column B if the corresponding cells in Column C are Odd Numbers?
=SUMPRODUCT(–(MOD(C2:C10,2)=1),B2:B10)
The result returns 1,100. There are two odd numbers in column C, and the corresponding cells in column B are B3 (800) and B5 (300).
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)=1): If the remainder equals one, the number is an odd number and the result returns TRUE; otherwise, it returns FALSE;
- Step 3: (–(MOD(C2: C10, 2)=1)): 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×1 + 588×0 + 300 × 1 + 300×0 + 200 × 0 + 300×0 + 800× 0 + 500×0 = 1,100
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.