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".

Project Name	Project Type	Amount	Anwser Should be
P1	Onshore	4,569	No
P2	Offshore	4,983	Yes
P1	Onshore	2,829	No
P2	Onshore	4,712	Yes
P3	Onshore	4,920	Yes
P3	Offshore	1,458	Yes
P3	Onshore	1,679	Yes
P4	Offshore	4,322	No
P5	Offshore	4,920	No
P5	Offshore	1,458	No

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

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!