Require formula to get status based on count and char

[harinsh]

Team,

I am looking for some formula to get the require project status "Yes" or "No". Here I have sample data. If you look at project name is repeating and project type have both onshore and offshore. I would need to get status "Yes" if any project worked on both project types. If in case project worked on only one project then I should get "No".

[TABLE="width: 337"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Project Name[/TD]
[TD]Project Type[/TD]
[TD]Amount[/TD]
[TD]Anwser Should be[/TD]
[/TR]
[TR]
[TD]P1[/TD]
[TD]Onshore[/TD]
[TD] 4,569[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD]Offshore[/TD]
[TD] 4,983[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]P1[/TD]
[TD]Onshore[/TD]
[TD] 2,829[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD]Onshore[/TD]
[TD] 4,712[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]P3[/TD]
[TD]Onshore[/TD]
[TD] 4,920[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]P3[/TD]
[TD]Offshore[/TD]
[TD] 1,458[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]P3[/TD]
[TD]Onshore[/TD]
[TD] 1,679[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]P4[/TD]
[TD]Offshore[/TD]
[TD] 4,322[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]P5[/TD]
[TD]Offshore[/TD]
[TD] 4,920[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]P5[/TD]
[TD]Offshore[/TD]
[TD] 1,458[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]

Can anyone please help on this.

Thank you

 

[Joe4]

Let's say that your data table is in the range A1:C11 (where row 1 is your title row).
Then enter this formula in cell D2 and copy down to D11:

=IF(AND(COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,"Onshore")>0,COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,"Offshore")>0),"Yes","No")

 

[harinsh]

Thank you very much...its working perfect !!

 

[Joe4]

You are welcome.
Glad I was able to help!