Site icon ExcelNotes

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:

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:

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.

Exit mobile version