Happy face icon

[Tahas]

Hi Friends,

I’m trying to use windings for smileys but only getting happy face. Please see table below:

Skills (E12)	Knowledge (F12)	Attitude (G12)	Behaviour (H12)	Circumstance (I12)	Staff Morale (J12
Y	Y	NA	Y	NA	Formula Result

<tbody>
</tbody>

In cell J12, I want to show a happy face when cells E12 though I12 either show “Y” or “NA” and if any cell show “N” then show sad face. My formula only works well for the happy face but not giving any sad face. I used following formula but only getting a happy face.

=IF(OR(E12="Y",E12="NA"),IF(OR(F12="Y",F12="NA"),IF(OR(G12="Y",G12="NA"),IF(OR(H12="Y",H12="NA"),IF(OR(I12="Y",I12="NA"),"J","L")))))

Any help will be greatly appreciated!

Thanks,
Taha

 

[shg]

	A​	B​	C​	D​	E​	F​	G​
1​	Skills​	Knowledge ​	Attitude ​	Behaviour ​	Circumstance ​	Staff Morale ​	​
2​	Y​	Y​	NA​	Y​	NA​	J​	F2: =IF(COUNTA(A2:E2) < 5, "", IF(SUMPRODUCT(COUNTIF(A2:E2, {"Y","NA"})) < 5, "L", "J"))
3​	Y​	Y​	NA​	N​	NA​	L​
4​						​

 

[Scott T]

try

=IF(ISNUMBER(MATCH("n",E12:I12,0)),"L","J")

 

[RobertSF]

Try this:

=IF(COUNTIF(E12:I12,"N")>0,"L","J")

The formula looks at the range E12:I12 and counts any cells that have "N" in them. If there are any such cells (>0), then put an "L" in the cell; otherwise, put a "J."

You have to set the font for this cell to Wingdings. The "L" is the sad face, and the J is the happy face.

Note that setting the font in a cell sets it not just for the displayed value but also for the underlying formula. So once you set the font to Wingding, if you try to edit the formula, it will just be a bunch of wingdings. To make sense of the formula, should you wish to change it, you have to change the font of the cell back to a readable font.

 

[Tahas]

Thank you so so much shg. It worked like a charm.

Much appreciated!
Taha

 

[Tahas]

Thank you all my friends. You all are truly amazing!

Hats off!
Taha