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), while a decimal number can contain digits other than 0 to the right of its decimal point. To figure out the count of cells with decimal numbers, you can use the ROUND and SUMPRODUCT functions.
The ROUND function can round a number to its nearest 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 1: How many cells are decimal numbers (e.g., 0.5, 1.3, 2.2)?
=SUMPRODUCT((ROUND(B2:B12,0)-B2:B12<>0)*1)
=SUMPRODUCT(–(ROUND(B2:B12,0)-B2:B12<>0))
- 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 a number is a decimal number 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.
Four cells are decimal numbers in column B.
If the range includes text strings, please use the formula below.
Formula 2: How many cells are decimal numbers?
=SUM(IF(ISNUMBER(B2:B12), IF(ROUND(B2:B12,0)-B2:B12<>0,1,0)))
The formula is an array formula, and you may 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.