I'm on O365 but these should work:
D2:=AGGREGATE(3,5,IntvwData[Interviewer])
D3:=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(IntvwData[[#Headers],[Candidate]],ROW(IntvwData[Candidate])-ROW(IntvwData[[#Headers],[Candidate]]),,1)), IF(IntvwData[Candidate]>"",MATCH("~"&IntvwData[Candidate],IntvwData[Candidate]&"",0))),ROW(IntvwData[Candidate])-ROW(IntvwData[[#Headers],[Candidate]])),1))
F2:=SUMPRODUCT(SUBTOTAL(3,OFFSET(IntvwData[Color Code],ROW(IntvwData[Color Code])-ROW(IntvwData[#Headers])-1,,1))*(IntvwData[Color Code]=$E2))
F3:=SUMPRODUCT(SUBTOTAL(3,OFFSET(IntvwData[Color Code],ROW(IntvwData[Color Code])-ROW(IntvwData[#Headers])-1,,1))*(IntvwData[Color Code]=$E3))
F4:=SUMPRODUCT(SUBTOTAL(3,OFFSET(IntvwData[Color Code],ROW(IntvwData[Color Code])-ROW(IntvwData[#Headers])-1,,1))*(IntvwData[Color Code]=$E4))
Note that the formula in D3 is an array formula entered with Ctrl+Shift+Enter.
WBD
Bookmarks