I have differnet repeated nos. in column A, and unique nos in column B. by
using countif function by puting the formula in column C1 i.e
=countif(A1:A30,B1) i can count the nos of B1 . but when i copy the same
formula at down rows by dragging the mouse then then the value of column is
changing from A2 :A31 , again in next cell it is A3:A32. i need used this
A1:A30 as constant this should not be change when i copy the same formula to
the down cells

Example:
A B C
211 211 =countif(A1:A30,B1)
123 123
211 511
511 258
258 111
211
111
111
123

Hi,

Please change the formula to =countif(A$1:A$30,B1) and then copy down.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"smina" wrote in message
...
I have differnet repeated nos. in column A, and unique nos in column B. by
using countif function by puting the formula in column C1 i.e
=countif(A1:A30,B1) i can count the nos of B1 . but when i copy the same
formula at down rows by dragging the mouse then then the value of column
is
changing from A2 :A31 , again in next cell it is A3:A32. i need used this
A1:A30 as constant this should not be change when i copy the same formula
to
the down cells

Example:
A B C
211 211 =countif(A1:A30,B1)
123 123
211 511
511 258
258 111
211
111
111
123

There are two solutions:

=COUNTIF(A:A,B1) if there is no other "junk" in column A below A30
or
=COUNTIF($A$1:$A$30,B1) if there is "junk" below A30

The first formula has the advantage that you do not need to change it if you
add values to your table!
--
Gary''s Student - gsnu200835


"smina" wrote:

I have differnet repeated nos. in column A, and unique nos in column B. by
using countif function by puting the formula in column C1 i.e
=countif(A1:A30,B1) i can count the nos of B1 . but when i copy the same
formula at down rows by dragging the mouse then then the value of column is
changing from A2 :A31 , again in next cell it is A3:A32. i need used this
A1:A30 as constant this should not be change when i copy the same formula to
the down cells

Example:
A B C
211 211 =countif(A1:A30,B1)
123 123
211 511
511 258
258 111
211
111
111
123

Put an absolute reference to A1:A30, like $A$1:$A$30
1) turn C1 into edit mode by pressing F2 key
2) put your cursor in between at A1 and press F4 key.
do the same for A30
--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you.


Thank You

cheers, francis










"smina" wrote:

I have differnet repeated nos. in column A, and unique nos in column B. by
using countif function by puting the formula in column C1 i.e
=countif(A1:A30,B1) i can count the nos of B1 . but when i copy the same
formula at down rows by dragging the mouse then then the value of column is
changing from A2 :A31 , again in next cell it is A3:A32. i need used this
A1:A30 as constant this should not be change when i copy the same formula to
the down cells

Example:
A B C
211 211 =countif(A1:A30,B1)
123 123
211 511
511 258
258 111
211
111
111
123