The logical values in Excel are true or false. To figure out the number of cells that are logical values, we will use the ISLOGICAL function and the SUMPRODUCT function.
The ISLOGICAL function is to test whether a cell has the values of true or false, and the SUMPRODUCT function returns the product of numbers in an array.
Example: In the following database, the first name is in column A, the second name is in column B, and the results are in column C. For practice purposes, the results in column C include different types of values.
Formula: To get the number of cells that are logical values in column C
=SUMPRODUCT(–ISLOGICAL(C2:C12))
- The ISLOGICAL function returns true if the cell is a logical value, and false if not;
- The double hyphens are to convert true to 1, and false to 0.
- The SUMPRODUCT function returns the sum of the products of the new array.
In the example, three cells are logical values (C6, C7 and C12).
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.
