An integer has no decimals or fractions, and it returns zero when an integer subtracts itself. An integer can be a positive natural number (e.g., 1, 2, 3), a negative natural number (e.g., -1, -2, -3), or zero (0).
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 integers in column C?
=SUMPRODUCT(–(C2:C10 – ROUND(C2:C10,0)=0), C2:C10)
The result returns -3,544.
Explanation:
- Step 1: ROUND(C2:C10,0): To round numbers and remove the decimals;
- Step 2: C2:C10-ROUND(C2: C10,0): The number minus its rounded integer;
- Step 3: (C2:C10-ROUND(C2:C10,0) =0): When a number minus its rounded integer and the result equals zero, the number is an integer, and the result returns TRUE; otherwise, the result returns FALSE;
- Step 4: (–(C2:C10 – ROUND(C2:C10,0)=0)): The double hyphen converts TRUE into one and FALSE into zero;
- Step 5: The SUMPRODUCT function returns the sum of the products of the new array and the values in column C.
| Profit/Loss | Step 1 | Step 2 | Step 3 | Step 4 |
|---|---|---|---|---|
| $782.03 | 782 | 0.03 | FALSE | 0 |
| $23.00 | 23 | 0 | TRUE | 1 |
| $576.74 | 577 | -0.26 | FALSE | 0 |
| -$163.00 | -163 | 0 | TRUE | 1 |
| $0.00 | 0 | 0 | TRUE | 1 |
| -$2,604.00 | -2604 | 0 | TRUE | 1 |
| $56.78 | 57 | -0.22 | FALSE | 0 |
| -$800.00 | -800 | 0 | TRUE | 1 |
| $0.00 | 0 | 0 | TRUE | 1 |
You can also sum the values in column B when the corresponding cells in column C are integers.
Question: What is the sum of the numbers in column B when the corresponding cells in Column C are integers?
=SUMPRODUCT(–(C2:C10 – ROUND(C2:C10,0)=0), B2:B10)
The result returns 2,900.
Explanation:
- Step 1: ROUND(C2:C10,0): To round numbers and remove the decimals;
- Step 2: C2:C10-ROUND(C2: C10,0): The number minus its rounded integer;
- Step 3: (C2:C10-ROUND(C2:C10,0) =0): When a number minus its rounded integer and the result equals zero, the number is an integer and the result returns TRUE; otherwise, the result returns FALSE;
- Step 4: (–(C2:C10 – ROUND(C2:C10,0)=0)): The double hyphen converts TRUE into one and FALSE into zero;
- Step 5: The SUMPRODUCT function returns the sum of the products of the new array and the values in column B.
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.