Excel Formula : Duplicate Multi condition

[chandrashekar]

Hello,

Need formula which should check Column A and B For Ex: If CPI have both A & B then its Yes. IF CPI has only A or B then it should be No.

BN	LDC	Formula1(Need Formula)	Output value
CPI	A		Yes
CPI	B		Yes
CPI	B		Yes
CPI1	A		No

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Regards,

Chandru

 

[Aladin Akyurek]

In C2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($B$2:$B$5<>"",IF($A$2:$A$5=A2,MATCH($B$2:$B$5,$B$2:$B$5,0))),ROW($B$2:$B$5)-ROW($B$2)+1),1))=SUM(IF(FREQUENCY(IF($B$2:$B$5<>"",MATCH($B$2:$B$5,$B$2:$B$5,0)),ROW($B$2:$B$5)-ROW($B$2)+1),1))

This delivers TRUE which can be read as Yes.

 

[Joe4]

Assuming BN is in column A, LDC is in column, and you want your Yes/No values in column C, enter this formula in cell C2 and copy down:

=IF(AND(COUNTIFS(A:A,A2,B:B,"A")>0,COUNTIFS(A:A,A2,B:B,"B")>0),"Yes","No")

 

[Ron Coderre]

With your sample data in A1:C5
Either of these regular formulas, copied down, can be used to return the values you're looking for:

C2: =IF(SUMPRODUCT(--(COUNTIFS($A$2:$A$5,A2,$B$2:$B$5,{"A","B"})>0))=2,"Yes","No")

or...if you're the kind of person who references entire columns...

C2: =IF(SUMPRODUCT(--(COUNTIFS($A:$A,A2,$B:$B,{"A","B"})>0))=2,"Yes","No")

Is that something you can work with?

 

[chandrashekar]

Hello All,

Its working fine. Thanks a lot

Regards,

Chandru