• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get total for two non-empty cells

YasserKhalil

Well-Known Member
Hello everyone
I have values in column A and there are blanks within these cells
I need to get the totals for two non-empty cells beside the current cell and the result would be put in adjacent column
For example the first value is in A1 and the second value is in A4 and the third value is in A6
Here in B6 I need to sum the A1 & A4 & A6

Then in A11 there is the value 11 so in B11 , I need to sum A4 & A6 & A11
Ans so on
Here's a sample of the desired results
Thanks advanced for help
 

Attachments

  • Test.xlsm
    12.2 KB · Views: 9
Code:
Sub Demo()
        Dim AD$, L&, VA
    With Sheet1
'           AD = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp)).Address
             AD = .UsedRange.Columns(1).Address
            VA = Filter(.Evaluate("TRANSPOSE(IF(" & AD & ">0,ROW(" & AD & ")))"), False, False)
        If UBound(VA) > 1 Then
                Application.ScreenUpdating = False
            For L = 2 To UBound(VA)
                .Cells(VA(L), 2).Value = Application.Sum(Range(.Cells(VA(L - 2), 1), .Cells(VA(L), 1)))
            Next
                Application.ScreenUpdating = True
        End If
    End With
End Sub
 
Thank you very much for these working and wonderful solutions
Both worked very well
I appreciate your incredible help
Kind Regards
 
Back
Top