Macro to Sum Cells in Two Columns
Results 1 to 8 of 8

Thread: Macro to Sum Cells in Two Columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to Sum Cells in Two Columns

    Hi All,

    I have a monthly spreadsheet that data will be in different columns. I want to sum the cells in two columns based on finding the header (which will always be the same text) and put the summed total in another column at the end of the spreadsheet. Can anyone help me with this please?

    Thanks in advance...


  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,860
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to Sum Cells in Two Columns

    Show us an example of your data and the expected solution. Be more specific in your explanation using your sample data for reference.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    New Member
    Join Date
    Apr 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to Sum Cells in Two Columns

    Quote Originally Posted by alansidman View Post
    Show us an example of your data and the expected solution. Be more specific in your explanation using your sample data for reference.
    Hi thanks for the reply.

    I would like a macro to find Cols B and C and enter summed total in col E


    Col1 FIND Col2 FIND Col3 Col4 Put Total of B and C In Here
    1 1 1 1 2
    2 2 2 2 4
    3 3 3 3 6
    4 4 4 4 8
    5 5 5 5 10

    Hope this helps

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,638
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Macro to Sum Cells in Two Columns

    How about
    Code:
    Sub TheOddGirl()
       Dim Fnd1 As Range, Fnd2 As Range, Cl As Range
       Dim NxtCol As Long, i As Long
       
       Set Fnd1 = Range("1:1").Find("Longitude", , , xlWhole, , , False, , False)
       Set Fnd2 = Range("1:1").Find("Northing", , , xlWhole, , , False, , False)
       If Fnd1 Is Nothing Or Fnd2 Is Nothing Then Exit Sub
       NxtCol = Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Column
       For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
          Cells(i, NxtCol) = Cells(i, Fnd1.Column) + Cells(i, Fnd2.Column)
       Next i
    End Sub
    Change search values in red to suit.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Apr 2018
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to Sum Cells in Two Columns

    Hi,

    Thank you so much that works perfect.

    I'll adapt it for my needs and it will save me so much time each week.

    Once again thank you so much, your'e a star x

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,638
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Macro to Sum Cells in Two Columns

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,315
    Post Thanks / Like
    Mentioned
    50 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Macro to Sum Cells in Two Columns

    How about

    Code:
    Sub Macro4()
        Dim n As Long
        n = Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Column
        With Range(Cells(2, n), Cells(Range("A" & Rows.Count).End(xlUp).Row, n))
            .FormulaR1C1 = "=IFERROR(INDEX(RC1:RC" & n - 1 & ",0,MATCH(""HEAD1"",R1,0))+" & _
                                    "INDEX(RC1:RC" & n - 1 & ",0,MATCH(""HEAD2"",R1,0)),"""")"
            .Value = .Value
        End With
    End Sub
    Regards Dante Amor

  8. #8
    Board Regular
    Join Date
    Sep 2005
    Posts
    5,097
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to Sum Cells in Two Columns

    Here's a formula (can also be run from a macro like Fluff and Dante's):

    Excel 2010
    ABCDE
    1Col1Include1Include2Col4Put Total of B and C In Here
    212618
    323174
    4386914
    5465111
    6519110

    Sheet1



    Worksheet Formulas
    CellFormula
    E2=SUM(SUMIF($B$1:$D$1,{"Include1";"Include2"},B2:D2))


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •