How to add odd numbers only in range

If you have a worksheet full of odd numbers and you want to add them, you can use this formula to get their sum. The formula {=SUM(Range*(MOD(Range,2)=1))} Do not type out of the brackets. Hold CTRL+shift, then press Enter while still in edit mode to create an array formula.

RANGE= is the range of values of which you would want to sum up all odd numbers.

What happens when used?

It will sum all odd numbers in a given range. Odd numbers are numbers that are not divided by two completely.

How the formula works.


The mod function is used to return a remainder of the number divided by 2. e.g., MOD(5,2) it will out 1. 5 divide 2 two it will have a reminder on 1. mostly MOD is used to test if the number is odd. The number is odd once proven by MOD(n,2)=1.

MOD(Range,2)= one will create an array of logical values that's TRUE or FALSE. The actual number can be divided by two, while FALSE cannot be divided by 2. Multiply the boolean array by the range to get an array where each value in the range is 0 or odd. T hats because N*TRUE equals N and N*FALSE equal 0 for any number N.

Steps on how to count the number of odd numbers within a range

Let's use the following set of data as our example

Enter the following formula in a blank cell =SUMPRODUCT((MOD(A1:C6,2)<>0)+0)

Press enter and the number of cells that contain odd numbers will be populated.

Steps on How to add odd numbers only in a range

1. Enter the following formula in any blank row =SUMPRODUCT(–(MOD(A1:C6,2)=1),A1:C6) . You can customize the range to match your data.

2. Press enters and the sum of all odd numbers within the selected range will be calculated.

  •  MOD function will match the remainder with 0 for every value within the range.
  • The negative sign will change TRUE values to FALSE and FALSE values to TRUE.
  • MOD function mostly takes a single input and output (remainder), but we want an array of values for the input within a range. Negative(–)characters help it do it.
  • SUMPRODUCT function will return the sum of the corresponding values to TRUE     values (as 1) and will ignore FALSE values
  •  A1:C6) is the range of values.
  • =1) that's the condition of the remainder.

There are a lot of ways, to sum up, the odd number and even. The two formulas work for both odd and even numbers. Some good at coding users can use python coding, to sum up, both odd numbers and numbers. It's easy to use. You only need a line of codes to work for you as a developer.

%d bloggers like this: