Sum of all number combinations excluding repetition Excel

Question

this top voted answer Sum all combinations Excel or Google Spreadsheets is close to what I need - however I require a solution where it keeps the combinations in order and excludes repetition based on this order. i.e. a data set of 1 2 3 4 would show the product of:

1 1+2 1+3 1+4 1+2+3 1+2+4 1+3+4 1+2+3+4

2 2+3 2+4 2+3+4

3 3+4

4

i.e

1 3 4 5 6 10

2 5 6

3 7

4

Is this possible in Google Sheets / Excel without a script?

Thanks

Yes just having a look at this... should there be others though like 1+2+4, 1+3+4, 2+3+4... ? I'm thinking a solution might involve counting in binary somehow and that there should be 2^4-1 combinations i.e.15. — Tom Sharpe, Apr 27 '18 at 10:44
Ah, yes - good point 1+2+4, etc is possible. Each number represents a food product and an associated chemical attribute, which can be blended with another product to result in the sum of the chemical from both. So yes, every unique combination is required without repetition. — William Stewart, Apr 27 '18 at 18:28

Tom Sharpe · Answer 1 · 2018-04-28T08:10:49.663

Here's a formula to display the sums in order

=ArrayFormula(sort(" "&transpose(split(SUBSTITUTE(substitute(textjoin("+",true,{if(mod(int(row(A1:A15)/(2^(column(A1:D1)-1))),2)=0,"",column(A1:D1)),B1:B15&";"}),"+;",";"),";+",";"),";")),1,true))

This formula will give the sum totals in the order that they are generated, but then it would have to be combined with the first formula to get them in the right order

=ArrayFormula(mmult(if(mod(int(row(A1:A15)/(2^(column(A1:D1)-1))),2)=0,0,column(A1:D1)),transpose(column(A1:D1))^0))

Giving this

=ArrayFormula(sort({" "&transpose(split(SUBSTITUTE(substitute(textjoin("+",true,{if(mod(int(row(A1:A15)/(2^(column(A1:D1)-1))),2)=0,"",column(A1:D1)),B1:B15&";"}),"+;",";"),";+",";"),";")),ArrayFormula(mmult(if(mod(int(row(A1:A15)/(2^(column(A1:D1)-1))),2)=0,0,column(A1:D1)),transpose(column(A1:D1))^0))},1,true))

Here's how it would look if you used actual values rather than column numbers

=ArrayFormula(sort({" "&transpose(split(SUBSTITUTE(substitute(textjoin("+",true,{if(mod(int(row(A1:A15)/(2^(column(A1:D1)-1))),2)=0,"",column(A1:D1)),if(row(A1:A15)>0,";")}),"+;",";"),";+",";"),";")),ArrayFormula(mmult(if(mod(int(row(A1:A15)/(2^(column(A1:D1)-1))),2)=0,0,A1:D1),transpose(column(A1:D1))^0))},1,true))

Hi Tom - this would appear to be a solution, however I am unsure how to implement it. It seems to reference the range A1:D1 and B1:B15. Entering values into col B does affect the output in col E but has no effect on the sum product in col F. Could you explain how I would use this for user entered values (note the real data would be values like 550.3, 240, 50.7, 130.1). Thank you for your time so far! — William Stewart, Apr 27 '18 at 22:48
Yes this is more of a demonstration and I think it would need a bit more work to be useful to you. It assumes Column B is empty at the moment and takes the values from the column numbers of columns A-D (hence 1-4) but it should be possible to modify it to take the values from a list instead. — Tom Sharpe, Apr 28 '18 at 07:31

score 0 · Answer 2 · answered May 03 '23 at 15:33

To get a true cross join or Cartesian product, use a recursive lambda function, like this:

=let( 
  values, torow(A2:A, 1), 
  zeros, map(values, lambda(_, 0)), 
  table, { zeros; values }, 
  first_, lambda(array, tocol(choosecols(array, 1), true)), 
  rest_, lambda(n, choosecols(table, sequence(1, columns(table) - n, n + 1))), 
  wrap_, lambda(array, wrapCount, wraprows(tocol(array, 1), wrapCount)), 

  cartesian_, lambda(a, b, wrap_( 
    byrow(a, lambda(row, 
      reduce(iferror(ø), sequence(rows(b)), lambda(acc, i, 
        { acc, row, chooserows(b, i) } 
      ) ) 
    ) ), 
    columns(a) + columns(b) 
  ) ), 

  iterate_, lambda( 
    self, a, b, if(iserror(b), a, 
      self(self, cartesian_(a, first_(b)), rest_(columns(a) + 1)) 
    ) 
  ), 

  cartesian, iterate_(iterate_, first_(table), rest_(1)), 
  sums, byrow(cartesian, lambda(row, sum(row))), 
  expressions, byrow(cartesian, lambda(row, join(" + ", filter(row, row)))), 
  result, unique(filter({ expressions, sums }, sums)), 
  key, choosecols(result, 1), 
  sort(result, left(key), true, len(key), true, key, true) 
)

The results will look like this:

data set	combinations	total
1	1	1
2	1 + 2	3
3	1 + 3	4
4	1 + 4	5
	1 + 2 + 3	6
	1 + 2 + 4	7
	1 + 3 + 4	8
	1 + 2 + 3 + 4	10
	2	2
	2 + 3	5
	2 + 4	6
	2 + 3 + 4	9
	3	3
	3 + 4	7
	4	4

To do the same with any values, such as text strings or dates, use this pattern:

=let( 
  values, torow(filter(to_text(A2:A), len(A2:A))), 
  blanks, map(values, lambda(_, "")), 
  table, { blanks; values }, 
  first_, lambda(array, tocol(choosecols(array, 1), true)), 
  rest_, lambda(n, choosecols(table, sequence(1, columns(table) - n, n + 1))), 
  wrap_, lambda(array, wrapCount, wraprows(tocol(array, 1), wrapCount)), 

  cartesian_, lambda(a, b, wrap_( 
    byrow(a, lambda(row, 
      reduce(iferror(ø), sequence(rows(b)), lambda(acc, i, 
        { acc, row, chooserows(b, i) } 
      ) ) 
    ) ), 
    columns(a) + columns(b) 
  ) ), 

  iterate_, lambda( 
    self, a, b, if(iserror(b), a, 
      self(self, cartesian_(a, first_(b)), rest_(columns(a) + 1)) 
    ) 
  ), 

  cartesian, iterate_(iterate_, first_(table), rest_(1)), 
  expressions, byrow(cartesian, lambda(r, join(" + ", filter(r, len(r))))), 
  result, tocol(expressions, 3), 
  sort(result, left(result), true, len(result), true, result, true) 
)

The results will look like this:

data set	combinations
a	a
b	a + b
c	a + c
d	a + d
	a + b + c
	a + b + d
	a + c + d
	a + b + c + d
	b
	b + c
	b + d
	b + c + d
	c
	c + d
	d

Sum of all number combinations excluding repetition Excel

2 Answers2

Linked