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, we will use the ROUND function and the SUMPRODUCT function.
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: To count the number of cells that are decimal numbers (e.g., 0.5, 1.3, 2.2)
- ROUND(B2:B12,0) is to round the numbers to the nearest integer.
- (ROUND(B2:B12,0)-B2:B12<>0) returns true when a number is a decimal number, and false if not.
- (ROUND(B2:B12,0)-B2:B12<>0)*1, or the double hyphens is to convert true into 1 and false into 0.
- The SUMPRODUCT function returns the sum of the products of the new array.
In the example, there are four cells that are decimal numbers in column B. If the range includes text strings, please use formula 2 below.
Formula 2: When counting the number of cells that are decimal numbers, and some cells are text strings, please use the following formula:
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.
=SUMPRODUCT(array1, [array2], …)
– Array1 is required, the first array is to multiply and then add.
– Array2 is optional, the second array is to multiply and then add.