How to Count a specific status which is mentioned in a cell in Google sheets

Question

I am new to Excel, I have a Question mentioned in image

I have multiple Status across same ID and I want to identify that if Status corresponding to that ID is Yes at least once I want to mark Final Status as Yes.

If Status is not Yes at least once then It should be counted as No, how can I do that in Google sheets

Image:

Answer 1

In Google Sheets, you could use:

=query({A:B},"select Col1,Max(Col2) where Col1 is not null group by Col1 order by Max(Col2) desc label Max(Col2) 'Final Status' ",1)

Answer 2

Try Countifs(). This will work both on Excel and google-sheets.

=IF(COUNTIFS($A$2:$A$14,D2,$B$2:$B$14,"Yes")>0,"Yes","No")