When you work with a worksheet with many different numbers, you may wonder how many are odd numbers and how many are even numbers.
Even numbers are those that are divisible by 2, while odd numbers are those numbers that are not divisible by 2. The MOD function returns the remainder when a number divides the divisor.
Example: You are working with a dataset with the product in column A, the date in column B, and the amount in column C.
Formula: How many cells are even numbers in column C?
=SUMPRODUCT(–(MOD(C2:C12,2)=0))
=SUMPRODUCT((MOD(C2:C12,2)=0)*1)
- Step 1: (MOD(C2:C12,2)=0): To check whether a number is divisible by 2. If the remainder is equal to 0, that is an even number, and the result returns true; otherwise, it returns false;
- Step 2: The double hyphen (or times one) is to convert true into one and false into zero;
- Step 3: The SUMPRODUCT function returns the sum of the products of the new array.
The result returns 5, so there are five even numbers in column C.
Notes: The MOD Function
Formula:
=MOD(number, divisor)
Explanations:
– The number is required, the number to find the reminder.
– The divisor is required, which is the divider.
