Macro to Sum Cells in Two Columns

TheOddGirl · Jul 7, 2019

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...

alansidman · Jul 7, 2019

Show us an example of your data and the expected solution. Be more specific in your explanation using your sample data for reference.

TheOddGirl · Jul 7, 2019

alansidman said:
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

<tbody>
[TD="class: xl63"]Col1[/TD]
[TD="class: xl64, width: 67"]FIND Col2[/TD]
[TD="class: xl64, width: 67"]FIND Col3[/TD]
[TD="class: xl63, width: 67"]Col4[/TD]
[TD="class: xl64, width: 170"]Put Total of B and C In Here[/TD]

[TD="class: xl63"]1[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl64"]1[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl64"]2[/TD]

[TD="class: xl63"]2[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl64"]2[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl64"]4[/TD]

[TD="class: xl63"]3[/TD]
[TD="class: xl64"]3[/TD]
[TD="class: xl64"]3[/TD]
[TD="class: xl63"]3[/TD]
[TD="class: xl64"]6[/TD]

[TD="class: xl63"]4[/TD]
[TD="class: xl64"]4[/TD]
[TD="class: xl64"]4[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl64"]8[/TD]

[TD="class: xl63"]5[/TD]
[TD="class: xl64"]5[/TD]
[TD="class: xl64"]5[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]10[/TD]

</tbody>

Hope this helps

Fluff · Jul 7, 2019

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("[COLOR=#ff0000]Longitude[/COLOR]", , , xlWhole, , , False, , False)
   Set Fnd2 = Range("1:1").Find("[COLOR=#ff0000]Northing[/COLOR]", , , 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.

TheOddGirl · Jul 7, 2019

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

Fluff · Jul 7, 2019

You're welcome & thanks for the feedback

DanteAmor · Jul 7, 2019

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(""[COLOR=#ff0000]HEAD1[/COLOR]"",R1,0))+" & _
                                "INDEX(RC1:RC" & n - 1 & ",0,MATCH(""[COLOR=#ff0000]HEAD2[/COLOR]"",R1,0)),"""")"
        .Value = .Value
    End With
End Sub

sheetspread · Jul 7, 2019

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

Excel 2010

A

B

C

D

E

1

Col1

Include1

Include2

Col4

Put Total of B and C In Here

2

1

2

6

1

8

3

2

3

1

7

4

3

8

6

9

14

5

4

6

5

1

11

6

5

1

9

1

10

Sheet1

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

Macro to Sum Cells in Two Columns

TheOddGirl

New Member

Excel Facts

alansidman

Well-known Member

TheOddGirl

New Member

Fluff

MrExcel MVP, Moderator

TheOddGirl

New Member

Fluff

MrExcel MVP, Moderator

DanteAmor

Well-known Member

sheetspread

Well-known Member

Similar threads

Forum statistics

Share this page

Macro to Sum Cells in Two Columns

New Member

Excel Facts

Well-known Member

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock