# Macro to Sum Cells in Two Columns

#### TheOddGirl

##### New Member
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?

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### alansidman

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

#### TheOddGirl

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

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

<tbody>
</tbody>

Hope this helps

#### Fluff

##### MrExcel MVP, Moderator
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

##### New Member
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

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

#### DanteAmor

##### Well-known Member

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

##### Well-known Member
Here's a formula (can also be run from a macro like Fluff and Dante's):