CODEWITHSUNDEEP
excelexcel-formulacountif
Joël A
Joël A

Reputation: 216

Excel COUNTIF with ID groups

I have in my Excel column A the IDs, each ID stands for a blog post, in column B the language of the blog and column C if it should be migrated. IDs can be the same up to 3 times because a blog post can be created in 3 different languages.

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

At the end I must be able to say if the ID group should be migrated and declare if ist due on the german version or another language version.

Output should look like this:

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

In itself very simple. I just don't know how to do this in an Excel formula if it has groups of maximum 3 entries with same IDs. In other words, the formula must respect the same IDs and compare their details.

Upvotes: 0

Views: 139

Answers (1)

JvdV
JvdV

Reputation: 75870

In 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")

Note your end result data does not resemble your input data on ID 43999.

Upvotes: 2

Related Questions