You are currently viewing How to add odd numbers only in range

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 equals 0 for any number N.

Steps on how to sum odd numbers within a range.

1. Separate the odd number and even number create a reminder column from the actual values by writing =MOD(B2,2). If you get 0, is even if you get one is odd. To make your     work easier, right-click at the right corner of the bottom and highlight the whole column.

2. to highlight odd from even. Write the following formula in a new column =IF(C2=1, B2,0). The formula will show all odd numbers.

3. You can now use the auto sum to calculate the whole sum. That is easy. But it will only work if you have one column to add. If you have more:

write this.

4. Select the cell you want the sum to be seen and write the following formula. =     SUMPRODUCT(–(MOD(A2:D7,2)=1), A2:D6)

5. MOD function will match the remainder with 0 for every value within the range.

6. The negative sign will change TRUE values to FALSE and FALSE values to TRUE.

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

8. SUMPRODUCT function will return the sum of the corresponding values to TRUE     values (as 1) and will ignore FALSE values

9. A2:D6) is the array of values.

10. =1) that's the condition of the remainder.

11. A2:D6 is the array.

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.