How to Sum Decimals

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/LossStep 1Step 2Step 3Step 4
$782.037820.03TRUE1
$23.00230FALSE0
$576.74577-0.26TRUE1
-$163.00-1630FALSE0
$0.0000FALSE0
-$2,604.00-26040FALSE0
$56.7857-0.22TRUE1
-$800.00-8000FALSE0
$0.0000FALSE0

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.

Leave a Reply