Count duplicates in two columns / Averageif question

Hi guys,

I have a couple of questions. Firstly, I was wondering how to count the number of duplicates in two columns. I have done conditional formatting to highlight the duplicates. However, an issue with this is that I only want to highlight it if the values in one of my columns is also in the other, and not in the same.

I have attached the excel file. I want to count how many of the numbers under class2 column occur at some point in class1. (overlap quantity)(total, not unique numbers). I also want to count how many don't occur in class 1. (non-overlap quantity).

Then, for those cells in the class2 column which do occur in class1, I want to average the total number of "cites5yr2" (overlap qual). For example, the value "514" in class2 (C column) occurs at least once in class1 (A column), so this should be highlighted as an "overlap". Then, for every corresponding value in the C column where there is this overlap, I want to include in my averageif function for the D column.

And once again, I want to average the "cites5yr2" column for those where the number in class2 didn't occur in class 1 (nonoverlap_qual).

I guess I have to use some kind of mix between countif, conditional formatting, and averageif?

Any help would be greatly appreciated.
Kind regards,
Chris