Compare cell values in same column with other conditions in Excel

0
votes

In below table in Excel, there are multiple columns, attaching 3 column just as an example. There are duplicates in ID column. Col1 has yes/no values.

I want to create a new column which will display Yes in case any value in Col1 is Yes for a particular ID.
E.g. if ID=1, if it contains at least one "yes" value in col1, then new_col should display yes.
If for an ID=3, all No, then new_col=No. I want to build an Excel formula. Please help.

ID    Col1    New_Col
1      Yes     Yes
1      No      Yes
2      Yes     Yes
3      No       No
3      No       No
3      No       No
4      No       No
1
excelexcel-formula
if(countifs()) should work for you. – OverflowStacker

1 Answers

2
votes

Use:

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