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.


[TABLE="width: 472"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]BN[/TD]
[TD]LDC[/TD]
[TD]Formula1(Need Formula)[/TD]
[TD]Output value[/TD]
[/TR]
[TR]
[TD]CPI[/TD]
[TD]A[/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]CPI[/TD]
[TD]B[/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]CPI[/TD]
[TD]B[/TD]
[TD] [/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]CPI1[/TD]
[TD]A[/TD]
[TD] [/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

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