Determine a "type" for each student based on combination of values

Dear Experts: Please help me write a formula on the following data. A student can be listed maximum 4 times in a row and a minimum on a 1 row.

Logic: If the Code = EXCL in all Terms then Type=Exclusive
If the Code = NONE in all Terms then Type=NONE
if the Codes are mix like in for studentid=100000002 then Type=Some



StudentID Term Code Type
100000000 Fall 2019 EXCL Exclusive
100000000 Spring 2020 EXCL Exclusive
100000000 Summer 2019 EXCL Exclusive
100000000 Winter 2020 EXCL Exclusive

100000001 Fall 2019 NONE None
100000001 Spring 2020 NONE None
100000001 Summer 2019 NONE None
100000001 Winter 2020 NONE None

100000002 Fall 2019 NONE Some
100000002 Spring 2020 NONE Some
100000002 Summer 2019 EXCL Some
100000002 Winter 2020 EXCL Some


Thank you for your help

Hi, welcome to the forum

(almost always better to upload a sample dummy workbook rather then post a pic, we cant really work with them)

Take a look at using some nested countiS() for this, something like...
=if(countif(A:A,A2)=countifs(A:A,A2,C:C,"EXCL"),"Exclusive",if(countif(A:A,A2)=countifs(A:A,A2,C:C,"NONE"),"None","Some))

Try:

Please Login or Register  to view this content.