When you work with a worksheet with many different numbers, you may wonder how many are odd numbers and how many are even numbers.
Odd numbers are not divisible by 2, while even numbers are those that are divisible by 2. The MOD function returns the remainder after dividing a number by 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 numbers odd numbers are in column C?
=SUMPRODUCT(–(MOD(C2:C12,2)=1))
=SUMPRODUCT((MOD(C2:C12,2)=1)*1)
- Step 1: (MOD(C2:C12,2)=1): To check whether a number is divisible by 2. If the remainder is equal to 1, that is an odd 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 6, and there are six odd numbers in column C (in light green).
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.
