Combining columns with If

[Usercode]

I have a range of columns (A1,B1,C1,D1). These columns have values from 1 to 20.
I have a formula in F1 like this:

=if($A1>7,"U",if($B1>7,"X",If($C1>7,"Y",if($D1>7,"Z",""))))

This formula puts the value of U,X,Y and Z if any of these cells (A1,B1,C1 or D1) is bigger than 7.

but it can't handle if there are two cells bigger than >7.

What should I add to this formula, if there are two numbers bigger than 7. So the end result will be a pair of U,X,Y or Z (comma separated).

Thanks.

 

[Eric W]

Try:

=IFERROR(LEFT(IF($A1>7,"U, ","")&IF($B1>7,"X, ","")&IF($C1>7,"Y, ","")&IF($D1>7,"Z, ",""),(COUNTIF($A1:$D1,">7")-1)*3+1),"")

 

[Usercode]

Thanks, Eric. This works beautifully!

 

[Peter_SSs]

If you have a version of Excel that has the TEXTJOIN function, you could also try this. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C	D	E
1	5	8	3	10	X,Z
2	10	10	2	10	U,X,Z
3
4	1	2	3	4
5	20	19	18	17	U,X,Y,Z
6		5	9		Y
Join

 

[Usercode]

Thanks, Peter. Unfortunately, I don't have TextJoin function. I use Office 2007.

 

[Peter_SSs]

Thanks, Peter. Unfortunately, I don't have TextJoin function. I use Office 2007.

Fair enough.

=IFERROR(LEFT(IF($A1>7,"U, ","")&IF($B1>7,"X, ","")&IF($C1>7,"Y, ","")&IF($D1>7,"Z, ",""),(COUNTIF($A1:$D1,">7")-1)*3+1),"")

You could simplify the removal of the extraneous comma & space, and avoid the need for IFERROR if the ", " is put before the letter each time.

=MID(IF($A1>7,", U","")&IF($B1>7,", X","")&IF($C1>7,", Y","")&IF($D1>7,", Z",""),3,99)

 

[Usercode]

Thanks again. This is simpler!

 

[Usercode]

Peter, A quick question, please.
I wanted to apply your formula some another table too. The formula I used is this:

=MID(IF($BA4>5,", E1","")&IF($BB4>5,", E2","")&IF($BC4>5,", E3","")&IF($BD4>5,", E4","")&IF($BE4>5,", E5","")&IF($BF4>5,", EE",""),3,99)

It work normally. but when any of the cells in the range is blank but with a formula which is

=IF(Intervals!$BV4=0,"",Intervals!$BV4)

, the formula counts it as if it is bigger than 5, and puts it in the result. Is there any way to get rid of this? please.

 

[Usercode]

Ok, I solved the problem. When I use the data on the main sheet, instead of reference sheet, it doesn't give the same error. Thanks.

 

[Peter_SSs]

Deleted. Sorry, wrong response.

Edit: This is what I meant to post.
If you did have formulas returning "", then for the original question, a solution would be

=MID(IF(N($A1)>7,", U","")&IF(N($B1)>7,", X","")&IF(N($C1)>7,", Y","")&IF(N($D1)>7,", Z",""),3,99)