Formula that outputs values based on the corresponding heading values of non empty cells

[Timicheckmate]

Good Afternoon everybody.

In this example, I have numbers in C1=1, D1=2, E1=3, F1=4 and G1=5 which represent the heading. I also have numbers in C3=19, D3=23, G3=59. I need a formular in cell I3 that outputs 125 based on the correspounding heading values of the non empty cells C3, D3 and G3.

[Pepe Le Mokko]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[FlameRetired]

Please try this in I3 and fill down.

Please Login or Register  to view this content.

	C	D	E	F	G	H	I
1	1	2	3	4	5		Result
2
3	19	23			59		125
4	10	21		42			124
5			36		52		35
6				48			4
7	11	29	37	45	57		12345

[Timicheckmate]

Good Morning FlameRetired.

Thank you for your response.
Formular does not work. #DIV/0! error.

[XLent]

If you really only have 5 results then

I3:
=(0&REPT(C$1,COUNT(C3))&REPT(D$1,COUNT(D3))&REPT(E$1,COUNT(E3))&REPT(F$1,COUNT(F3))&REPT(G$1,COUNT(G3)))+0
copied down

If, in reality, you have many more values then you will likely need to use a UDF (if not using O365 etc), as any numeric solution is likely to be constrained by significant digit limitation.

[Timicheckmate]

Thank you so much XLent.

Formular works but I have modified the example as attached. The heading now includes numbers from 0 to 9. The cell contents below the heading can be of any format i.e. heading format is number and contents below heading can be number or text. It appears that the formular only works for number format.

[Timicheckmate]

Thank you so much XLent.

Formular works but I have modified the example as attached. The heading now includes numbers from 0 to 9. The cell contents below the heading can be of any format i.e. heading format is number and contents below heading can be number or text. It appears that the formular only works for number format. Also the results are more than 5.

[XLent]

So, you can modify / extend the prior suggestion by replacing the COUNT with COUNTA, e.g.:

=REPT(B$1,COUNTA($B3))&REPT(C$1,COUNTA($C3))&REPT(D$1,COUNTA($D3))&REPT(E$1,COUNTA($E3))&REPT(F$1,COUNTA($F3))&REPT(G$1,COUNTA($G3))&REPT(H$1,COUNTA($H3))&REPT(I$1,COUNTA($I3))&REPT(J$1,COUNTA($J3))&REPT(K$1,COUNTA($K3))

or, if you have null strings to contend with, <>"" or 1-COUNTBLANK($B3)

the numeric approach proposed by flameretired (below) will still work for 10 elements but you'd have to wrap result in a TEXT call, e.g.:

=TEXT(SUMPRODUCT(10^(COUNTA(B3:K3)-ROW(INDIRECT("1:"&COUNTA(B3:K3))))*AGGREGATE(15,6,$B$1:$K$1/(B3:K3<>""),ROW(INDIRECT("1:"&COUNTA(B3:K3))))),REPT("0",COUNTA(B3:K3)))

[FlameRetired]

Or wrap BASE around it.

Please Login or Register  to view this content.

If the number of numbers (row 1) varies I would consider using a dynamic named range. Then define other ranges based upon that.

	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	0	1	2	3	4	5	6	7	8	9		Result		Original
2
3	FO	19	23			59			TR			01258		01258
4		10	21		42					Z		1249		1249
5	YX			36		52		DS				0357		0357
6			F		48		JH		VE			2468		2468
7		11	29	37	45	57						12345		12345

[Timicheckmate]

So, you can modify / extend the prior suggestion by replacing the COUNT with COUNTA, e.g.:

=REPT(B$1,COUNTA($B3))&REPT(C$1,COUNTA($C3))&REPT(D$1,COUNTA($D3))&REPT(E$1,COUNTA($E3))&REPT(F$1,COUNTA($F3))&REPT(G$1,COUNTA($G3))&REPT(H$1,COUNTA($H3))&REPT(I$1,COUNTA($I3))&REPT(J$1,COUNTA($J3))&REPT(K$1,COUNTA($K3))

This formular works perfectly for what I am trying to do.
Thank you again.

[Timicheckmate]

Or wrap BASE around it.

Please Login or Register  to view this content.

If the number of numbers (row 1) varies I would consider using a dynamic named range. Then define other ranges based upon that.

This also works. Thanks soo much @FlameRetired.

[FlameRetired]

You are welcome. Thank you for the feedback and for marking your thread Solved.