An integer is the number zero (0), a positive natural number (e.g., 1, 2, 3), or a negative integer with a minus sign (e.g., -1, -2, -3). You can use the ROUND and SUMPRODUCT functions to figure out the number of cells with integers.
The ROUND function can round a number to its integer, and the SUMPRODUCT function returns the product of numbers in an array.
Example: In the following database, the city is in column A, and the temperature is in column B.
Formula: How many cells are integers (e.g., 0, 1, 2) in column B?
=SUMPRODUCT((ROUND(B2:B12,0)-B2:B12=0)*1)
=SUMPRODUCT(–(ROUND(B2:B12,0)-B2:B12=0))
The result returns 7, so seven numbers are integers in column B.
- Step 1: ROUND(B2:B12,0) is to round the numbers to the nearest integer;
- Step 2: (ROUND(B2:B12,0)-B2:B12=0) returns true when the number is an integer, and false if not;
- Step 3: The double hyphen (or times 1) is to convert true into one and false into zero;
- Step 4: The SUMPRODUCT function returns the sum of the products of the new array.
If your data includes the text strings, the above formula will return an error. Please use the formula below:
=SUM(IF(ISNUMBER(B2:B12), IF(ROUND(B2:B12,0)-B2:B12=0,1,0)))
The formula is an array formula, and you will need to type "Ctrl+Shift+Enter" after entering the formula into a cell.
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 then add.
– Array2 is optional, the second array is to multiply and then add.