VBA to find Sum of Even Numbers and Odd Numbers
Question: In excel a column has odd and even numbers, I need to display the sum of odd numbers in particular cell and sum of even numbers in a particular cell….using macros
Answer:
You can use simple Excel formula to have the sum of Even or Odd Numbers in a cell.
Excel Formula to find Sum of Even Numbers and Odd Numbers
However, you can use VBA to sum EVEN or ODD numbers in a particular Column or Range. Here are example macro to find Sum of Even or Odd numbers in a given Column.
Steps to find Sum of Even Numbers using VBA
 Decide the Column to Sum the Even Numbers
 Loop through the each cell in the Column
 Check if the cell value is Even Number or not
 You can use ‘Cell Mod 2=0’ to check if Cell is Even Number
 Add all Even numbers to a temporary variable
 Finally print or show the Sum of Even numbers at required range
Excel VBA Macro to find Sum of Even Numbers
Here is the example macro to sum all Even values in a column:
1
2
3
4
5
6
7
8

Sub sbSumEvenNumbers() LastRow = .Cells(.Rows. Count , "A" ). End (xlUp).Row totSum = 0 For i = 1 To LastRow If Range( "A" & i) Mod 2 = 0 Then totSum = totSum + Range( "A" & i) Next Range( "B1" ) = totSum ' Print Even Number at B1 End Sub 
How this works?
 LastRow = .Cells(.Rows.Count, “A”).End(xlUp).Row: We are finding the last row in a given Columns (A)
 totSum = 0 : Initiating the temporary variable (totSum) to store the sum of values
 For i = 1 To LastRow: Looping through the each cell in the given Column (A)
 If Range(“A” & i) Mod 2 = 0 : Checking if a cell value is Even
 Then totSum = totSum + Range(“A” & i): Summing to the temporary Variable
 Range(“B1”) = totSum: Printing the Sum of Even numbers at B1
Steps to find Sum of Odd Numbers using VBA
 Decide the Column to Sum the Odd Numbers
 Loop through the each cell in the Column
 Check if the cell value is Odd Number or not
 You can use ‘Cell Mod 2=1’ to check if Cell is Odd Number
 Add all Odd numbers to a temporary variable
 Finally print or show the Sum of Odd numbers at required range
VBA to find Sum of Odd Numbers
Here is the example macro to sum all Odd values in a column:
1
2
3
4
5
6
7
8

Sub sbSumOddNumbers() LastRow = .Cells(.Rows. Count , "A" ). End (xlUp).Row totSum = 0 For i = 1 To LastRow If Range( "A" & i) Mod 2 = 1 Then totSum = totSum + Range( "A" & i) Next Range( "B2" ) = totSum ' Print Odd Number at B2 End Sub 
How this works?
 LastRow = .Cells(.Rows.Count, “A”).End(xlUp).Row: We are finding the last row in a given Columns (A)
 totSum = 0 : Initiating the temporary variable (totSum) to store the sum of values
 For i = 1 To LastRow: Looping through the each cell in the given Column (A)
 If Range(“A” & i) Mod 2 = 1 : Checking if a cell value is Odd
 Then totSum = totSum + Range(“A” & i): Summing to the temporary Variable
 Range(“B2”) = totSum: Printing the Sum of Odd numbers at B2
VBA to find Sum of Even and Odd Numbers
Here is the example macro to sum all Even and Odd values in a column:
1
2
3
4
5
6
7
8
9
10
11
12
13
14

Sub sbSumEvenAndOddNumbers() LastRow = .Cells(.Rows. Count , "A" ). End (xlUp).Row totSumEven = 0 totSumOdd = 0 For i = 1 To LastRow If Range( "A" & i) Mod 2 = 0 Then totSumEven = totSumEven + Range( "A" & i) ElseIf Range( "A" & i) Mod 2 = 1 Then totSumOdd = totSumOdd + Range( "A" & i) End If Next Range( "B1" ) = totSumEven ' Print Even Number at B1 Range( "B2" ) = totSumOdd ' Print Odd Number at B2 End Sub 
How this works?
 LastRow = .Cells(.Rows.Count, “A”).End(xlUp).Row: We are finding the last row in a given Columns (A)
 totSumEven = 0 : Initiating the temporary variable (totSumEven ) to store the sum of Even values
 totSumOdd = 0 : Initiating the temporary variable (totSumOdd ) to store the sum of Odd values
 For i = 1 To LastRow: Looping through the each cell in the given Column (A)
 If Range(“A” & i) Mod 2 = 0 : Checking if a cell value is Even
 Then totSumEven = totSumEven + Range(“A” & i): Summing even numbers to the temporary Variable
 If Range(“A” & i) Mod 2 = 1 : Checking if a cell value is Odd
 Then totSumOdd = totSumOdd + Range(“A” & i): Summing Odd numbers to the temporary Variable
 Range(“B1”) = totSumEven : Printing the Sum of Even numbers at B1
 Range(“B2”) = totSumOdd : Printing the Sum of Odd numbers at B2
Site looks great.
Awesome, this was an exceptionally nice post. Spending some time and actual effort to produce a very good article