How to Sum Integers

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

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.

Leave a Reply