Sorting data containing COUNTIF formulas

Hi,

I have COUNTIF fomulas on a spreadsheet that reference table arrays on another sheet. The criteria is just referencing cells. I.e. Column A contains ID numbers and I want to count how many times each ID number appears on the second sheet. So for example, cell A1 has a reference, say "ABC1" and cell B1 has =COUNTIF(Sheet2!$A$1:$A$100,A1).

If I copy B1 down I get the desired result.

But...when I sort on the column containing the COUNTIFs something crazy is happening to the formulas...in that it doesn't sort on the output of the COUNTIF to show 0,0,0,1,1,1,2,3, etc as intended. Instead, my COUNTIF's start referencing cells in different rows so the formula in B1 would read =COUNTIF(Sheet2!$A$1:$A$100,A97)....and row 1 in my sheet would be counting how many times the reference in A97 appears in the other sheet?????

I hope that makes sense! Any ideas how I get round this?

Many thanks

Thanks Martin, it works!