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.
{=SUM(Range*(MOD(Range,2)=1))}
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 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 code to work for you as a developer.
SUM, IF, MOD, and ARRAY Formula
Steps,
1. MOD
The MOD function returns the remainder after a number is divided by a divisor. We know that an odd number divided by two leaves a remainder of one. While even numbers divided by two leave no remainder, this is how excel distinguishes odd from even numbers.
Consider a list of numbers, the formula in cell D4 is =MOD(C4,2)
All the odd numbers have a remainder of one and that of even numbers is zero.
2. IF
In column E, we enter the IF formula =IF(D4=1,C4,0)
This then lists all the odd numbers in column C and has zero in place of even numbers.
In cell, F4 type the array formula
=SUM(IF(MOD($C$4:$C$17,2)=1,$C$4:$C$17))
Kutools for Excel
If you have Kutools for Excel, you can quickly select the odd rows or columns and then view the calculations in the status bar.
Steps;
1. Select the whole range of rows and then click Kutools then Select, then, Select Interval Rows & Columns.
2. In the dialogue box, check the Rows or Columns as you need and type 1 into both the textbox, and uncheck Select entire rows.
3. Click OK, now all the odd rows are selected, if you go to the status bar you can view the summing results.
SUM, OFFSET Formula
=SUM(2*ROW(OFFSET($C$4,,,100000/2))-1)
This formula works on the principle of making an array of odd numbers between 1-100 and adding them up.
Steps;
1. Make an array from 1 to 50
2. In a spare cell say A1, type =ROW(OFFSET($A$1,,,100/2)) then press F9 instead of Enter. Excel will respond with an array
={ 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14………….; 40; 41; 42; 43; 44; 45; 46; 47; 48; 49; 50}
3. In a spare cell, D6, type =OFFSET($A$1,,,100/2) then press F9 instead of Enter. Excel will respond with an array
={0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; ………..0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
4. The array contains 50 zeros and the expanded formula
=ROW(OFFSET($A$1,,,100/2))
5. In a spare cell, D8, type =2*ROW(OFFSET($A$1,,,100/2)) then press F9 instead of Enter. This function is used to double the array values. The excel will respond
= {2; 4; 6; 8; 10; 12; 12; 14; 16; 18; 20; …………80; 82; 84; 86; 88; 90; 92; 94; 94; 96; 98; 100}
6. In a spare cell, D10, type =2*ROW(OFFSET($A$1,,,100/2))-1 then press F9 instead of Enter. The excel will respond with an array of odd numbers from 1-100
7. The formula =SUM(2*ROW(OFFSET($A$1,,,100/2))-1) will be typed in cell D12 then press F9 instead of Enter.
This function is used to add up the array of odd numbers.