# Select combination of cells, display total in another cell

#### ken1921

##### New Member
Hi

I need to achieve the following.

Cells A1 to A5 have numeric values in them. I want to be able to select different combinations and display the total of the selected cells in A6 - eg A1+A3+A4 the first time, A2+A5 the second etc etc

I'm assuming I will need VBA to do this

Many thanks

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
You can use IF statements
So assuming D1 is the option switch
 1 = A1+A3+A5 2 = A2+A5 3 = A1+A2+A3 4 = A3+A4+A5

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

The formula for A6 would be =IF(D1=1,A1+A3+A5,IF(D1=2,A2+A5,IF(D1=3,A1+A2+A3,IF(D1=4,A3+A4+A5,SUM(A1:A5)))))

I thought of that but wouldn't it be a very long formula with loads of combinations if all eventualities were covered. Isn't there a more elegant way of doing it in vba

Hi, here is one option you can try. Select the cells you want to sum and then run the macro.

Code:
``````Sub sumselection()
Dim r As Range
Set r = Intersect(Selection, Range("A1:A5"))
If Not r Is Nothing Then Range("A6").Value = Evaluate("=SUM(" & r.Address & ")")
End Sub``````

Last edited:
Put this code in sheet's code module:
You will get the result automatically when you select 1 or more cells in "A1:A5".
Code:
``````Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
Range("A6") = WorksheetFunction.Sum(Selection)
End If
End Sub``````

Replies
2
Views
250
Replies
5
Views
535
Replies
6
Views
260
Replies
3
Views
499
Replies
8
Views
191

### Forum statistics

1,196,487
Messages
6,015,476
Members
441,898
Latest member
kofafa ### 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.

### Which adblocker are you using?    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

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