Want formula to return specific value and discard else than specifc.

With a helper column, in Z2, copied down:

=COUNTIF(X:X,X2)

in AB2, an array formula, copied down:
=IFERROR(INDEX(X:X,SMALL(IF(($Y$2:$Y$8={"Red","Blue"})*($Z$2:$Z$8=2)*(COUNTIF($AB$1:AB1, $X$2:$X$8)=0),ROW($Y$2:$Y$8)),ROWS($1:1))),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

...or since you have the latest version of Excel, an ordinary formula:

=IFERROR(INDEX(X:X,AGGREGATE(15,6,ROW($Y$2:$Y$8)/(($Y$2:$Y$8={"Red","Blue"})*($Z$2:$Z$8=2)*(COUNTIF($AB$1:AB1, $X$2:$X$8)=0)),ROWS($1:1))),"")

... still needing the helper.

Ignore the two previous replies... they're wrong.

helper in Z2:
=AND(COUNTIFS($X:$X,X2)=2,SUM(COUNTIFS(X:X,X2,Y:Y,{"red","blue"}))=2)

non-array formula in AB2:
=IFERROR(INDEX(X:X,AGGREGATE(15,6,ROW($X$2:$X$8)/(($Z$2:$Z$8=TRUE)*(COUNTIF($AB$1:AB1, $X$2:$X$8)=0)),ROWS($1:1))),"")

No. Post a sample sheet that shows clearly what is to be counted and what is not. I do not uuserstand this bit at all:

But if its having "Gold,Premium,Silver,Labor or any of others than Gold/Premium/Silver" then its out of my criteria.

However, explain the complete criteria. Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

A simpler one. Helper:
=SUM(COUNTIFS(A:A,A2,B:B,{"Full","Partial","Pemium","Labor"}))

Main formula:
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$8)/(($C$2:$C$8=0)*(COUNTIF($E$1:E1, $A$2:$A$8)=0)),ROWS(E$2:E2))),"")

see sheet.