countif and data sorting errors

[maacmaac]

I have countif statements in column D. The code in cell D2 is as follows:

Please Login or Register  to view this content.

When I do a sort, for example, on column C, the countif formula does not sort correctly. As an example the code in cell D2 changes from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

I looked at this again and again and can't figure out why it is doing this. I need the formula in each cell to remain the same. In assistance is appreciated. Thanks

[Dulanic]

Not sure which way you want it... but lock the cell? $B$2 If you want the formula static with the sorting. Or include the column with the formula in the sorting if thats what your trying to do also. Not clear which way you want to go.

[maacmaac]

I thought of locking the cell, which would work, but the only problem with that is I have a spreadsheet with ~ 9000 rows of data and I need to apply the formula to all 9000 cells. If I lock the cell, I am not able to drag the formula down.

I am including the column in the sort, but that still doesn't seem to work. Any other suggestions. Thanks.

[Dulanic]

If you don't sort the formula, the source cells stay the same so it should automatically change no? Guess I am confused. Either way, do a find and replace. So find A and replace with $A$ which will lock the whole column of values. Replace A with the applicable column.

[maacmaac]

Still can't figure out why the sort isn't working. I have attached an example spreadsheet that will hopefully describe what the issue is.

In the attached, I have two tables that need to be sorted.

The first table in the spreadsheet (columns A:C) is using a countif formula in columns C and the table being used for the countif formula is on the same sheet as the table.

The second table in the spreadsheet (columns I:K) is using a countif formula in columns K and the table being used for the countif formula is on a different sheet as the table.

If you click on the sort command (sort by account number) for each table, the first table sorts correctly (column C) while the second table sorts incorrectly (column K).

Any insight as to why I would get two different results for the same sort is appreciated because I can't figure it out. Thanks. (using excel 2000)

[Dulanic]

As I was kind of getting at before, I didn't see a reason to sort the formula before, and now I especially don't after seeing the formula. Change the code to:

Please Login or Register  to view this content.