How to Return Dynamic Array of Each Sum for 3 or more combinations of numbers?

[user47]

Hello,

Have not seen this posted before so I hope it is not a stupid question.

If I have a column with numbers and I wanted to return the sum of combinations of every 2 numbers I can do the following:

Book1
	A	B	C	D	E	F	G	H
3		11977	6562	1679	6642	6623	3953	5755
4	11977	23954	18539	13656	18619	18600	15930	17732
5	6562	18539	13124	8241	13204	13185	10515	12317
6	1679	13656	8241	3358	8321	8302	5632	7434
7	6642	18619	13204	8321	13284	13265	10595	12397
8	6623	18600	13185	8302	13265	13246	10576	12378
9	3953	15930	10515	5632	10595	10576	7906	9708
10	5755	17732	12317	7434	12397	12378	9708	11510
Sheet1

Cell Formulas
Range	Formula
B4:H10	B4	=$A$4:$A$10+TRANSPOSE($A$4:$A$10)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7:AT8,B9:K15,R9:AT15,M16:Q22,F46:AT46,AK16:AT45,B4:L6,R4:AT6,M47:AT48	Cell Value	=$A$2	text	NO
B7:AT8,B9:K15,R9:AT15,M16:Q22,F46:AT46,AK16:AT45,B4:L6,R4:AT6,M47:AT48	Cell Value	duplicates	text	NO

Of course these have duplicates off either side of the diagonal- I would just ignore one side and can analyze the other (haven't figured out a way to return only unique values in the table).
But what if I wanted to take it a step further and return the sums for every unique combination of sets of three numbers instead? I can't figure out how to do this in excel except manually which is very time consuming and prone to mistakes. Is this possible? This is just an example but my actual dataset would be about 150 numbers and I need to add them up in 3s or even 4s or 5s. Is there a general formula I can use to do this?

Thanks for any help/ ideas- appreciated as usual!!

 

[awoohaw]

Hi, is this what you are looking for?

mr excel questions 17.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		22	50	38	32	3	22	7	25	23	33		3rd Dimension	Sum 3 Dimensions
2	37	59											45	104
3	29		79										35	114
4	46			84									33	117
5	28				60								17	77
6	41					44							4	48
7	22						44						46	90
8	30							37					43	80
9	9								34				33	67
10	2									25			35	60
11	8										41		29	70
User47

Cell Formulas
Range	Formula
B2:K11	B2	=IF(ROW(B2)=COLUMN(B2),B$1+$A2,"")
N2:N11	N2	=IF(ISNUMBER(INDEX($B$2:$K$11,ROW(N2)-1,ROW(N2)-1)),M2+INDEX($B$2:$K$11,ROW(N2)-1,ROW(N2)-1),"")

 

[awoohaw]

Hi, is this what you are looking for?

mr excel questions 17.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		22	50	38	32	3	22	7	25	23	33		3rd Dimension	Sum 3 Dimensions
2	37	59											45	104
3	29		79										35	114
4	46			84									33	117
5	28				60								17	77
6	41					44							4	48
7	22						44						46	90
8	30							37					43	80
9	9								34				33	67
10	2									25			35	60
11	8										41		29	70
User47

Cell Formulas
Range	Formula
B2:K11	B2	=IF(ROW(B2)=COLUMN(B2),B$1+$A2,"")
N2:N11	N2	=IF(ISNUMBER(INDEX($B$2:$K$11,ROW(N2)-1,ROW(N2)-1)),M2+INDEX($B$2:$K$11,ROW(N2)-1,ROW(N2)-1),"")

I just realized your title was for a dynamic array. The above is not that. But it should work, unless you are seeking elegance.

 

[Fluff]

How about

=LET(d,A4:A10,r,ROWS(d),IF(SEQUENCE(r)>=SEQUENCE(,r),d+TRANSPOSE(d),""))

 

[user47]

Hi, is this what you are looking for?

mr excel questions 17.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		22	50	38	32	3	22	7	25	23	33		3rd Dimension	Sum 3 Dimensions
2	37	59											45	104
3	29		79										35	114
4	46			84									33	117
5	28				60								17	77
6	41					44							4	48
7	22						44						46	90
8	30							37					43	80
9	9								34				33	67
10	2									25			35	60
11	8										41		29	70
User47

Cell Formulas
Range	Formula
B2:K11	B2	=IF(ROW(B2)=COLUMN(B2),B$1+$A2,"")
N2:N11	N2	=IF(ISNUMBER(INDEX($B$2:$K$11,ROW(N2)-1,ROW(N2)-1)),M2+INDEX($B$2:$K$11,ROW(N2)-1,ROW(N2)-1),"")

Hello, thank you for your reply but unfortunately this is not what I was looking for.

Maybe I have not explained my question correctly.

Let me try again: Is there a way to add up every unique combination of say 3 numbers in a list of say 10 numbers (1,2,3,4,5,6,7,8,9,10) and have the sum of them dynamically appear in a range? I can't picture what a table would look like beyond a combination of every 2 numbers as in my first post.

I am having trouble inserting my example as a table for some reason so I have put a picture instead.

Hope this defines the problem better- If not let me know and I will try to add further clarity.
Let me know and thank you again for your help!

 

[user47]

How about

=LET(d,A4:A10,r,ROWS(d),IF(SEQUENCE(r)>=SEQUENCE(,r),d+TRANSPOSE(d),""))

Hello,

This works well for adding 2 numbers- my goal is I am not sure how to add every combination of 3 numbers together and what such a table would look like.
Basically, I'm looking for a 3-numbers-added-together version of your solution.
I have added an example in a reply above if you want to reference that.

Thank you!

 

[Fluff]

Can you post an example of the result you want as the formulae I supplied does what you asked for.

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2
3		11977	6562	1679	6642	6623	3953	5755
4	11977	23954
5	6562	18539	13124
6	1679	13656	8241	3358
7	6642	18619	13204	8321	13284
8	6623	18600	13185	8302	13265	13246
9	3953	15930	10515	5632	10595	10576	7906
10	5755	17732	12317	7434	12397	12378	9708	11510
Master

Cell Formulas
Range	Formula
B4:H10	B4	=LET(d,A4:A10,r,ROWS(d),IF(SEQUENCE(r)>=SEQUENCE(,r),d+TRANSPOSE(d),""))
Dynamic array formulas.

 

[KolGuyXcel]

Is this what you are looking for, for a dynamic range?

 

[user47]

Can you post an example of the result you want as the formulae I supplied does what you asked for.

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2
3		11977	6562	1679	6642	6623	3953	5755
4	11977	23954
5	6562	18539	13124
6	1679	13656	8241	3358
7	6642	18619	13204	8321	13284
8	6623	18600	13185	8302	13265	13246
9	3953	15930	10515	5632	10595	10576	7906
10	5755	17732	12317	7434	12397	12378	9708	11510
Master

Cell Formulas
Range	Formula
B4:H10	B4	=LET(d,A4:A10,r,ROWS(d),IF(SEQUENCE(r)>=SEQUENCE(,r),d+TRANSPOSE(d),""))
Dynamic array formulas.

Hello,

The formula works well for 2 numbers added up- I am looking for a solution that adds every unique combination of 3 numbers say in a list of 10 numbers.

The mathematical unique number of combinations will be 120 unique sums but I am not sure how to create a list of them in excel in a way that shows each of the sets of the 3 numbers that were summed for the 120 sums. It is difficult for me to give an example as I cannot picture what such a table would look like. I did provide that table of adding every 2 numbers but that was just a simple example and not the problem at hand. It is for 3+ number combinations that I'm stumped about.

This video on youtube shows the closest thing I am looking for but I think it's a paid tool:

Let me know if this helps with my explaining what the issue is.

Thank you.

 

[user47]

View attachment 88426

Is this what you are looking for, for a dynamic range?

Hello,

I think it does! Can you help me understand your table a little bit? I think it looks correct although I was not able to follow the addition logic in every cell (you're probably correct as I am just slow at this!) The solution should show the sum of every unique 3-number combination eg, 1+2+3=6, 1+2+4=7, 1+2+5=8 etc. I can see it there for some of the cells but not all (again my limitation not yours!). Would you be mind sharing the formula as I think this was a picture?

Thank you!