How to Count Cells that are Integers

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). To figure out the count of cells with integers, we will use the ROUND function and the SUMPRODUCT function.

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 1: To count the number of cells that are integers (e.g., 0, 1, 2)

=SUMPRODUCT((ROUND(B2:B12,0)-B2:B12=0)*1)
=SUMPRODUCT(--(ROUND(B2:B12,0)-B2:B12=0))

  • ROUND(B2:B12,0) is to round the numbers to the nearest integer.
  • (ROUND(B2:B12,0)-B2:B12=0) returns true when the number is an integer, 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 seven cells that are integers in column B. If you find the counting range includes text strings, please use formula 2 below.

Formula 2: When counting the number of cells that are integers, and some cells are text strings, please use the following formula:

=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.

Leave a Reply