Countif formulas change after doing a sort
 

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(D1:D100,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

Countif formulas change after doing a sort
 

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!
================================================== ===

"Bob Smith" wrote in message
nk.net...
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(D1:D100,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

Countif formulas change after doing a sort
 

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

Bob

"RagDyeR" wrote in message
...
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!
================================================== ===

"Bob Smith" wrote in message
nk.net...
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(D1:D100,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

Countif formulas change after doing a sort
 

Thanks for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Bob Smith" wrote in message
nk.net...
Thanks RD, that did work and my thanks for your timely reply.

Bob

"RagDyeR" wrote in message
...
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!
================================================== ===

"Bob Smith" wrote in message
nk.net...
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(D1:D100,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