conditional sum

ajith

Board Regular
Joined
Nov 21, 2012
Messages
215
Office Version
  1. 2016
Platform
  1. Windows
Hello,
How to sum all third values of a range consisting of a single column and paste the result after the last cell of that column. The range within that column is not constant.

Thank you,
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi, you don't specify which third value so this will add up each one, you can decide which you want to keep

Code:
Sub SumEvery()
Dim lRow As Long
Dim tCol As Variant
Dim r As Long
Dim result1, result2, result3 As Long
tCol = 1 ' Use letter or number to represent the column


lRow = Cells(Rows.Count, tCol).End(xlUp).Row


For r = 2 To lRow
    Select Case Cells(r, tCol).Row Mod 3
        Case Is = 0
            result1 = result1 + Cells(r, tCol)
        Case Is = 1
            result2 = result2 + Cells(r, tCol)
        Case Is = 2
            result3 = result3 + Cells(r, tCol)
    End Select
Next r


Cells(lRow + 1, tCol).Value = result1 & " - " & result2 & " - " & result3


End Sub
 
Upvote 0
sir,
There could be varying number of values in a column. But each cell will contain only one value. So if we consider from A5 to A1048 let us take that there are values upto A15. Like 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15. If we want to sum every third row of this column ie. 3+6+9+12+15 which gives 45. This 45 has to come in A16. The answer should come in the next row where the data ends.
In another situation the values may be there upto 99 or even more but the number of rows involved will be in multiples of 3.

Thanks in advance
 
Upvote 0
In the code I provided please change

Code:
Cells(lRow + 1, tCol).Value = result1 & " - " & result2 & " - " & result3

to

Code:
Cells(lRow + 1, tCol).Value = result1

or if you really have no use for the two other sets of three then strip them out..

Code:
Sub SumEvery()
Dim lRow As Long
Dim tCol As Variant
Dim r As Long
Dim result As Long


tCol = 1 ' Use letter or number to represent the column


lRow = Cells(Rows.Count, tCol).End(xlUp).Row


    For r = 3 To lRow Step 3
        result = result + Cells(r, tCol)
    Next r


Cells(lRow + 1, tCol).Value = result


End Sub
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top