How to find all possible sums of two numbers in a list in Excel?
If you need to find all possible sums of two numbers in a list or list all possible combinations of two lists in Excel, there are no built-in functions to achieve this directly. This guide introduces a VBA code solution for calculating sums and the List All Combinations utility in Kutools for Excel for handling combinations efficiently.
Find all possible sums of two numbers in a list with VBA code
Find all possible combinations of two lists with List All Combinations
Find all possible sums of two numbers in a list with VBA code
If you want to find all possible sums of two numbers in a list, you can apply below VBA code.
1. Press Alt + F11 keys to display the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, copy and paste below code to the module.
VBA: List all possible sums of two numbers in a list
Sub Combinations()
'UpdatebyExtendoffice20180628
Dim xRgArr
Dim xNum As Long
Dim I, J, K As Long
Dim xTemp As Double
Dim xRg As Range
Dim xRgCount As Long
Dim xDic As New Dictionary
On Error Resume Next
Set xRg = Application.InputBox("Select a list (one column):", "KuTools for Excel", Selection.Address, , , , , 8)
If (xRg Is Nothing) Or (xRg.Count = 1) Then Exit Sub
xRgCount = xRg.Count
K = 1
ReDim xRgArr(1 To xRgCount)
For Each xCell In xRg
xRgArr(K) = xCell.Value
K = K + 1
Next
K = 0
For I = 1 To xRgCount
For J = I + 1 To xRgCount
xTemp = xRgArr(I) + xRgArr(J)
If Not xDic.Exists(xTemp) Then
xDic.Add CDbl(xTemp), CStr(xTemp)
K = K + 1
End If
Next
Next
Range("C1").Resize(xDic.Count, 1) = Application.WorksheetFunction.Transpose(xDic.Keys)
End Sub
3. Click Tools > References, in the popping dialog, check Microsoft Scripting Runtime checkbox.
 |  |  |
4. Click OK to close the dialog, then press F5 to run the code. A dialog will appear, prompting you to select a list for calculating the possible sums.
5. Click OK, then all possible sums of two numbers from the list will be displayed in column C.
Tip: In the code string Range("C1").Resize(xDic.Count, 1), you can change C1 to any other cell where you want to place the results.
Find all possible combinations of two lists with List All Combinations
If you want to list all possible combinations of two lists, as shown in the screenshot below, the List All Combinations utility of Kutools for Excel can do you a favor.
1. Click Kutools > Insert > List All Combinations.
2. In the List All Combinations dialog, click the select button to select the first list you will combine.
3. Click Add to add the list into Combinations list section.
4. Repeat step 2 and 3 to add the second list to the Combinations list.
5. Then click Ok to select a cell to place the combination result.
6. Click OK. All possible combinations have been listed in cells.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Demo: Find all possible combinations of two lists with Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite ā Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license ā set up in minutes (MSI-ready)
- Works better together ā streamlined productivity across Office apps
- 30-day full-featured trial ā no registration, no credit card
- Best value ā save vs buying individual add-in