VBA to Find Sum of Even Numbers and Odd Numbers

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

  1. Decide the Column to Sum the Even Numbers
  2. Loop through the each cell in the Column
  3. Check if the cell value is Even Number or not
  4. You can use ‘Cell Mod 2=0’ to check if Cell is Even Number
  5. Add all Even numbers to a temporary variable
  6. 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

  1. Decide the Column to Sum the Odd Numbers
  2. Loop through the each cell in the Column
  3. Check if the cell value is Odd Number or not
  4. You can use ‘Cell Mod 2=1’ to check if Cell is Odd Number
  5. Add all Odd numbers to a temporary variable
  6. 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

2 thoughts on “VBA to Find Sum of Even Numbers and Odd Numbers”

  1. Awesome, this was an exceptionally nice post. Spending some time and actual effort to produce a very good article

Leave a Comment

Your email address will not be published. Required fields are marked *