Rank IF

[noletwin1]

I need to rank employees based on Product % and what their feeling they are in. I want to rank all the happy and only happy or rank all the content.

Employee	001	002	003	004	007	008	011	012	021	024	026	032	033	037	038	041	042	043	047	049	050	052	053	055	056	058	060	062	064	067	072	074	075	076	077	078	080	081	083	088	089	090	091	099	101	104	106	125	132	133	140	143	155	156	157	159	161	165	172	199	220	221	222	224	245	248	251	252	254	255	256	257	258	259	264	266	267	269	270	273	274	276	277	278	279	281	282	283	285	286	287	288	289		290	291
Feeling	Happy	Happy	Happy	Happy	Content	Happy	Happy	Happy	Happy	Sad	Happy	Content	Content	Happy	Happy	Happy	Happy	Happy	Happy	Content	Happy	Sad	Happy	Happy	Sad	Happy	Happy	Sad	Happy	Happy	Happy	Happy	Happy	Happy	Content	Sad	Happy	Happy	Happy	Happy	Happy	Happy	Happy	Sad	Sad	Happy	Content	Sad	Content	Content	Content	Happy	Happy	Happy	Content	Happy	Content	Sad	Happy	Other	Happy	Sad	Content	Content	Happy	Sad	Sad	Sad	Content	Upset	Upset	Content	Upset	Upset	Upset	Upset	Sad	Upset	Content	Upset	Upset	Upset	Upset	Upset	Upset	Upset	Content	Upset	Upset	Upset	Upset	Upset	Upset		Upset	Upset
Product %	22%	1%	2%	20%	21%	2%	3%	5%	13%	1%	7%	5%	3%	5%	7%	7%	4%	1%	1%	4%	3%	3%	2%	11%	1%	6%	3%	3%	15%	12%	1%	6%	4%	8%	6%	4%	1%	10%	5%	1%	1%	11%	25%	14%	3%	17%	3%	0%	3%	6%	8%	7%	6%	10%	2%	6%	17%	14%	6%	19%	0%	0%	1%	4%	3%	3%	0%	0%	30%	23%	29%	15%	25%	54%	10%	16%	14%	15%	39%	23%	24%	24%	29%	17%	34%	30%	30%	21%	39%	23%	17%	19%	15%		13%	30%
Happy Rank

<colgroup><col><col span="95"></colgroup><tbody>
</tbody>

Content Rank
Sad Rank

 

[Eric W]

Maybe:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Employee	1	2	3	4	7	8	11	12	21	24	26	32
2	Feeling	Happy	Happy	Happy	Happy	Content	Happy	Happy	Happy	Happy	Sad	Happy	Content
3	Product %	22%	1%	2%	20%	21%	2%	3%	5%	13%	1%	7%	5%
4	Happy	2	34	31	3		31	27	22	6		13
5	Content					4							10
6	Sad										9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Array Formulas Cell Formula B4 {=IF(B$2=$A4,MIN(IF(B$3=LARGE(IF($B$2:$CS$2=$A4,$B$3:$CS$3,""),ROW(INDIRECT("1:"&COUNTIF($B$2:$CS$2,$A4)))),ROW(INDIRECT("1:"&COUNTIF($B$2:$CS$2,$A4))))),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Marcelo Branco]

Or

Put in A4:A6 just the keywords, Happy, Content and Sad

	A​	B​
4​	Happy​	2​
5​	Content​
6​	Sad​

Formula in B4
=IF(B$2=$A4,COUNTIFS($B$3:$CS$3,">"&B$3,$B$2:$CS$2,$A4)+1,"")

copy across and down

M.

 

[Eric W]

Much better!

 

[noletwin1]

Thank you!!