IF function help!

[nholagi]

Hi! I couldn't figure out how to write an IF function (or any function) that can show result like in the "outcome" column.

Basically if a row has 0 across then it should say "not in range", if there is a value in that row then return that column header name, if there are more than 2 value return then it should say "Multiple"
any thought?
thanks

 

[kweaver]

It would help if you updated your profile to indicate what version of Excel you are running.

Here's one way.

Book8
	A	B	C	D	E	F	G
1	A	B	C	D	E	F	outcome
2	1	0	0	0	0	0	A
3	0	0	0	0	0	0	Not in range
4	0	1	0	0	1	0	Multiple
5	0	0	0	1	0	0	D
Sheet2

Cell Formulas
Range	Formula
G2:G5	G2	=IF(0=SUM(A2:F2),"Not in range",IF(1<SUM(A2:F2),"Multiple",INDEX($A$1:$F$1,MATCH(TRUE,INDEX(A2:F2<>0,),0))))

 

[Fluff]

@nholagi What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[nholagi]

@nholagi What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

thank you! i just did

 

[Fluff]

Thanks for that.
As you have 365, another option

Fluff.xlsm
	A	B	C	D	E	F	G
1	A	B	C	D	E	F	outcome
2	1	0	0	0	0	0	A
3	0	0	0	0	0	0	Not in Range
4	0	1	0	0	1	0	Multiple
5	0	0	0	1	0	0	D
Report

Cell Formulas
Range	Formula
G2:G5	G2	=LET(f,FILTER($A$1:$F$1,A2:F2=1,"Not in Range"),IF(COLUMNS(f)>1,"Multiple",f))

 

[nholagi]

It would help if you updated your profile to indicate what version of Excel you are running.

Here's one way.

Book8
	A	B	C	D	E	F	G
1	A	B	C	D	E	F	outcome
2	1	0	0	0	0	0	A
3	0	0	0	0	0	0	Not in range
4	0	1	0	0	1	0	Multiple
5	0	0	0	1	0	0	D
Sheet2

Cell Formulas
Range	Formula
G2:G5	G2	=IF(0=SUM(A2:F2),"Not in range",IF(1<SUM(A2:F2),"Multiple",INDEX($A$1:$F$1,MATCH(TRUE,INDEX(A2:F2<>0,),0))))

thank you!

 

[nholagi]

Thanks for that.
As you have 365, another option

Fluff.xlsm
	A	B	C	D	E	F	G
1	A	B	C	D	E	F	outcome
2	1	0	0	0	0	0	A
3	0	0	0	0	0	0	Not in Range
4	0	1	0	0	1	0	Multiple
5	0	0	0	1	0	0	D
Report

Cell Formulas
Range	Formula
G2:G5	G2	=LET(f,FILTER($A$1:$F$1,A2:F2=1,"Not in Range"),IF(COLUMNS(f)>1,"Multiple",f))

this work! thanks. but i never use this function before, do you mind explain how this work so i understand it for future uses?

 

[Fluff]

Which function are you talking about?

 

[nholagi]

Which function are you talking about?

This: =LET(f,FILTER($A$1:$F$1,A2:F2=1,"Not in Range"),IF(COLUMNS(f)>1,"Multiple",f))

 

[Fluff]

There are a number of functions in that formula, which one are you talking about.