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

Hi Thangavel,

Thanks a lot for your reply! Sorry, I think you misunderstood. I believe this is summing all of the values which are duplicated. I only want to know how many of the values in class2 at some point occur in class1. For example, considering cell C2 (514) we can see that, for example, cell A9 is also (514). Then, I count all the 514's, and see that there are 38. Then, for example, I look at the number of 435's (which occurs at least once in class1, for example, cell A2). I see that there are 5. I do this for all the different values which occur in the class2 column, then sum them, so 38 + 5 + ...

The total sum of this is what I want for OVERLAP_QUANT. A similar method for NONOVERLAP_QUANT, but then simply the ones which don't occur.
Any ideas?

for 568: class1 = 7, class2 = 1 so count is 7 or 1 ?

Sorry, I made this unclear. I am only interested in the count of duplicate values of column C (class2).

I'll try again...

how many times the value from column class2 is in the class1 column

class1	class2	Count
514	514	19000
435	435	1105
424	424	127
546	546	58
549	549	48
210	210	10
568	568	7

is that what you want?

I think you should improve your description to make it more clear and transparent