How to find all combinations of numbers that have a sum in a range in Excel?

I have a question about Excel.

If I have a set of numbers, for example:

How can I find all combinations of numbers with a sum in a range (120 to 160) ????

For example:

The result will give me all combinations of all numbers in a combination in range (of 120 to 160):
(80,79) , (16,22,26,27,79), (26,27,79), ....

I tried to use Kutools to find the combinations, but it just supported finding all combinations that equal a given number (not in a range).

Kutools guide

I believe you need to brute force loop through all possible combinations, so be aware this can take a long time to run if you have a long list of numbers. It became noticeably slow for me if the list was around 20 numbers.

If you have n numbers in the list, there are 2(n-1) possible combinations of those numbers, and that scales very rapidly. For a list of 8 numbers, there are 255 combinations. For a list of 22 numbers, there are 4,194,303 combinations.

In the macro, we loop through each of those combinations. We assign a power of 2 to each number in the list (1,2,4,8,16...), and this allows us to test whether each number belongs to each possible combination. We add up the total for each combination, check whether it falls within our desired range, and add it to a collection. At the end of the macro we loop through the collection and print it to the sheet.

Sub all_combinations()

Dim arrNumbers As Variant, arrTemp As Variant
Dim collOutput As Collection

Dim FirstNumber As Range, Output As Range
Dim LowLimit As Long, HiLimit As Long

Dim i As Long
Dim CurrentComb As Long, CombTest As Long, CombTotal As Long
Dim NumPower As Long
Dim arrIndex As Long

'Create collection for output
Set collOutput = New Collection

'Assign ranges
Set FirstNumber = Range("D2")   'First number in list
LowLimit = Range("G3")  'Lower limit
HiLimit = Range("H3")   'Upper limit
Set Output = Range("O5")    'First cell to write results

'Assign numbers to array
arrNumbers = Range(FirstNumber, FirstNumber.End(xlDown)).Value

'Resize temporary array
ReDim arrTemp(1 To UBound(arrNumbers))

'Loop through all possible combinations 1 to 2^(n-1)
For CurrentComb = 1 To 2 ^ (UBound(arrNumbers) - 1)
    
    ReDim arrTemp(1 To UBound(arrNumbers))  'Clear temporary array
    CombTest = CurrentComb  'Assign limit for power-of-2 numbers
    
    'Loop through number list
    For i = UBound(arrNumbers, 1) To 1 Step -1
        
        NumPower = 2 ^ (i - 1)  'Assign power-of-2 to each position in the number list
        
        'Check if power-of-2 number is less than current limit
        If NumPower <= CombTest Then
            arrIndex = arrIndex + 1 'move to next position in temp array
            arrTemp(arrIndex) = arrNumbers(i, 1)    'write current number to temp array
            CombTotal = CombTotal + arrNumbers(i, 1)   'Update current combination total
            CombTest = CombTest - NumPower  'Update current power-of-2 limit
        End If
    
    Next i
    
    'Check if current combination total is within low / high limit
    If CombTotal >= LowLimit And CombTotal <= HiLimit Then
        collOutput.Add arrTemp  'Add temp array to output collection
    End If
    
    'clear combination total, reset temp array index
    CombTotal = 0
    arrIndex = 0

Next CurrentComb

'Write to sheet
Dim item As Variant, itemPart As Variant
Dim CurrentRow As Long, CurrentCol As Long

CurrentRow = Output.Row

'Loop through each item in collection
For Each item In collOutput
    CurrentCol = Output.Column
    'Loop through each item in each array
    For Each itemPart In item
        Cells(CurrentRow, CurrentCol).Value = itemPart
        CurrentCol = CurrentCol + 1
    Next itemPart
    CurrentRow = CurrentRow + 1
Next item

End Sub

this can be achieved with Solver Add-in...shall be 4 lines of code

Solver reset
Solverok
Solveradd
solvertest

Watch this

let me know in case you need me code wrt your problem, please attach the file with dummy data or data example