Countif formulas change after doing a sort

[Bob Smith]

I'm experiencing a bit of problem recently in a few of my workbooks. 100
Formulas are in the H column counting entries in column D. The name of the
sites are in column G with the formulas in column H. The formulas are to
count the site names in top 100 listings in column d out of appx 3000
entries. The formula is =COUNTIF(D1100,G5) going all the way down to cell
G105. When I do an descending sort on those cells in columns G & H, the
formulas in some cells will change to any variable of d1: through d11: and
:d90 to :d110 or any other number for that matter.

After doing the sorts, I can fix the formulas by selecting the H Column,
then doing a Edit - Replace d?: to d1:, and then :d9? to :d100 & :d1?? to
:d100.

My question is why do these formulas change after doing a sort. It's a PITA
to fix those formulas each time I do a sort on the columns.

Any help or advice to fix this snafu would be appreciated.

Bob

 

[RagDyeR]

If I understand what you're asking, have you tried absolute references?

=COUNTIF($D$1:$D$100,G5)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I'm experiencing a bit of problem recently in a few of my workbooks. 100
Formulas are in the H column counting entries in column D. The name of the
sites are in column G with the formulas in column H. The formulas are to
count the site names in top 100 listings in column d out of appx 3000
entries. The formula is =COUNTIF(D1100,G5) going all the way down to cell
G105. When I do an descending sort on those cells in columns G & H, the
formulas in some cells will change to any variable of d1: through d11: and
:d90 to :d110 or any other number for that matter.

After doing the sorts, I can fix the formulas by selecting the H Column,
then doing a Edit - Replace d?: to d1:, and then :d9? to :d100 & :d1?? to
:d100.

My question is why do these formulas change after doing a sort. It's a PITA
to fix those formulas each time I do a sort on the columns.

Any help or advice to fix this snafu would be appreciated.

Bob

 

[Bob Smith]

Thanks RD, that did work and my thanks for your timely reply.

Bob

 

[RagDyer]

Thanks for the feed-back.