Need help with Formula

[krazybo13]

Hi, i need help with a formula that will give me different tiers depending on the skills each person based on tiers 1, 2 and 3

Example if they know the first 5 columns they will be tier 1, if they know the first 7 they will be tier 2 etc

 

[etaf]

how about something like

=IF(COUNTIF(C2:O2,"X")=13,"Tier 5",IF(COUNTIF(C2:M2,"X")=11,"Tier 4",IF(COUNTIF(C2:K2,"X")=9,"Tier 3",IF(COUNTIF(C2:I2,"X")=7,"Tier 2",IF(COUNTIF(C2:G2,"X")=5,"Tier 1","No Tier")))))

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2			x	x	x	x	x										Tier 1
3			x	x	x	x	x	x	x								Tier 2
4			x	x	x	x	x	x	x	x	x						Tier 3
5			x	x	x	x	x	x	x	x	x	x	x				Tier 4
6			x	x	x	x	x	x	x	x	x	x	x	x	x		Tier 5
7																	No Tier
Sheet1

Cell Formulas
Range	Formula
Q2:Q7	Q2	=IF(COUNTIF(C2:O2,"X")=13,"Tier 5",IF(COUNTIF(C2:M2,"X")=11,"Tier 4",IF(COUNTIF(C2:K2,"X")=9,"Tier 3",IF(COUNTIF(C2:I2,"X")=7,"Tier 2",IF(COUNTIF(C2:G2,"X")=5,"Tier 1","No Tier")))))

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2			x	x	x	x	x										Tier 1
3			x	x	x	x	x	x	x								Tier 2
4			x		x	x	x	x	x	x	x						No Tier
5			x	x	x	x	x		x	x	x	x	x				Tier 1
6			x	x	x	x	x	x	x	x		x	x		x		Tier 2
7																	No Tier
Sheet1

Cell Formulas
Range	Formula
Q2:Q7	Q2	=IF(COUNTIF(C2:O2,"X")=13,"Tier 5",IF(COUNTIF(C2:M2,"X")=11,"Tier 4",IF(COUNTIF(C2:K2,"X")=9,"Tier 3",IF(COUNTIF(C2:I2,"X")=7,"Tier 2",IF(COUNTIF(C2:G2,"X")=5,"Tier 1","No Tier")))))

 

[krazybo13]

It worked like a charm!!!

etaf you rock!!!​

 

[etaf]

you are welcome