A decimal is a number that consists of a whole and a fractional part, and the result will not return to zero when a decimal minus its integer portion. You can combine the ROUND and SUMPRODUCT functions to determine the number of cells with decimals.
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 decimals in column C?
=SUMPRODUCT(–(C2:C10 – ROUND(C2:C10,0)<>0), C2:C10)
The result returns 1415.55.
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 does not equal zero, the number is a decimal 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 | TRUE | 1 |
| $23.00 | 23 | 0 | FALSE | 0 |
| $576.74 | 577 | -0.26 | TRUE | 1 |
| -$163.00 | -163 | 0 | FALSE | 0 |
| $0.00 | 0 | 0 | FALSE | 0 |
| -$2,604.00 | -2604 | 0 | FALSE | 0 |
| $56.78 | 57 | -0.22 | TRUE | 1 |
| -$800.00 | -800 | 0 | FALSE | 0 |
| $0.00 | 0 | 0 | FALSE | 0 |
You can also sum the values in column B when the corresponding cells in column C are decimals.
Question: What is the sum of the numbers in column B when the corresponding cells in Column C are decimals?
=SUMPRODUCT(–(C2:C10 – ROUND(C2:C10,0)<>0), B2:B10)
The result returns 2,166.
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 does equal zero, the number is a decimal 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.