Count if function to find repeated

[yasir]

Dear Friends,


I have used countif function to find repeated item i a column. for example


Emp No


100

100

100


when i apply the countif function i got result as below.

( Count if)

100 3

100 2

100 1


I want to get the result in revers as below


100 1

100 2

100 3


Could you please help me ..

 

[NARAYANK991]

Hi yasir ,


If you apply the COUNTIF function using absolute addressing , you will get one result , if you apply it without absolute addressing , you will get another result.


Assume your data is as follows :


31

43

56

43

31

56

32

33

45

43

56


Assume this is from F6 to F16 ; now if you have a formula like =COUNTIF(F6:F16,F6) in G6 , and you copy it downwards , till G16 , your results will be as follows :


2

3

3

2

1

2

1

1

1

1

1


This is because when you copy the formula from G6 to G7 , the formula is changed by Excel to =COUNTIF(F7:F17,F7)


But if you use absolute addresses , and have the formula =COUNTIF($F$6:$F$16,F6) , then when you copy this to G7 , it will become COUNTIF($F$6:$F$16,F7) ; the addresses with the $ sign will not be changed.


To get the result that you are looking for , use the formula =COUNTIF($F$6:$F$16,F6)-COUNTIF(F6:F16,F6)+1 in G6 , and copy it down.


Narayan

 

[Luke M]

You could also just do this:

=COUNTIF(F$7:F7,F7)

and copy down as needed.