带有ID组的Excel COUNTIF

Question

我的Excel列A中有ID，每个ID代表一个博客帖子，在B列中是博客的语言，如果需要迁移，则使用C列。in最多可以是3次，因为博客文章可以用3种不同的语言创建。

​

ID  Language    Migration
44021   German  Yes
44021   French  No
44021   Italian No
44014   German  No
44014   French  Yes
44014   Italian No
43999   German  No
43999   French  Yes
43999   Italian Yes
44001   French  Yes
55504   German  No
55504   French  No

​

最后，我必须能够说出ID组是否应该迁移，并声明是在德语版本上还是在其他语言版本上。。

输出应该如下所示：

​

ID  Language    Migration   Result
44021   German  Yes Due to german
44021   French  No  Due to german
44021   Italian No  Due to german
44014   German  No  Due to other lang
44014   French  Yes Due to other lang
44014   Italian No  Due to other lang
43999   German  Yes Due to german
43999   French  Yes Due to german
43999   Italian Yes Due to german
44001   French  Yes Due to other lang
55504   German  No  No Migration
55504   French  No  No Migration

​

其本身非常简单。我只是不知道如何在Excel公式中这样做，如果它有最多3个条目的组，具有相同的in。换句话说，公式必须尊重相同的ID并比较它们的细节。

Answer 1

在D2中

=IF(COUNTIFS(A:A,A2,C:C,"Yes"),IF(COUNTIFS(A:A,A2,B:B,"German",C:C,"Yes"),"Due to German","Due to other lang"),"No Migration")

注意，您的最终结果数据与ID 43999上的输入数据不相似。