I am trying to count the frequency of values using countif. In sheet1 I have a long list (around 35,000) of values, some are duplicated thousands of times, others hundreds, others not at all. I want to find out how many times each value appears. I copied and pastes the whole column into sheet2, removed duplicates then did a countif =countif(sheet1!$A$1:$A$3500,sheet2!A1).

Yet it brings back some odd results. One value which only appears twice in sheet1 is counted too many times and in sheet 2 says there are over 30,000. When I add up all the countif results it's over 217,000! It's counting too many values. Does anyone know why?