Is this possible with an array formula?

beyond belief

New Member
I have a list of numbers in B2:B20 and I want to create an array of all the combinations of the sums of the list so that I can lookup this array and match a particular number

B2:B3 would be number 1, B2:B4 would be number 2 and so on

Thank you

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Why not add a column with the formula =sum(B2:B3) in cell C3 and copy down.

Then search on that?

Hi beyond belief,
Welcome to the board.
beyond belief said:
I have a list of numbers in B2:B20 ...
...B2:B3 would be number 1, B2:B4 would be number 2 and so on
Do you only need the sum of B2:B3, B2:B4, B2:B5 etc.
or do you also need the sum of B3:B4, B3:B5,.. B17:B18, B5+B8+B19 ?

In the first case you could use tactps's formula, but make B2 absolute : =SUM(\$B\$2:B3)
The second case is quite another problem (an array with all possible combinations). IMHO, the trick to do this is a technique called 'column generation' A start could be : http://www.mrexcel.com/board2/viewtopic.php?t=59786

Replies
1
Views
314
Replies
12
Views
509
Replies
5
Views
262
Replies
1
Views
134
Replies
10
Views
439

1,203,468
Messages
6,055,599
Members
444,801
Latest member
JurieDT

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.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

Follow these easy steps to disable AdBlock Plus

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