Adding of Count in Excel

Question

How do I increment the row count (Cnt) based on the Number column in Excel? Are there any formulas that I can use to generate the Cnt column?

Cnt  Number
1    IF1234
1    IF1234
2    IF4444
2    IF4444
3    IF5555
3    IF5555

Question isn't exactly clear. Where are you getting the count from? — CamelSlack
– CamelSlack, Commented Oct 13, 2011 at 3:01
Are you saying, for example, the value IF4444 implies Cnt=2? If not, why is Cnt=2 the expected value on those rows? If we knew why Cnt is supposed to be 2 here, we might be able to tell you if Excel can do that. — VoidStar
– VoidStar, Commented Oct 13, 2011 at 3:12
Hi, The 'Number' field is is a list of records that I have generated from some database but I need to compute the 'Cnt' field one . Row 1 and Row 2 are with the same Number values so I have to set Cnt as 1 , Row 3 and 4 are another set of same Number values so I have to increment the Cnt to 2 and so on. I am not sure how to increment the 'Cnt' if the 'Number' fields are the same. — snowfrost
– snowfrost, Commented Oct 13, 2011 at 3:29

Excellll · Accepted Answer · 2011-10-13 04:14:37Z

2

If the values in the Number column are sorted (i.e., grouped), you can do the following.

If, however, the values in the Number column are NOT sorted, you will need to do the following:

Enter "1" in A2.
Enter =IF(ISERROR(MATCH(B3,B$2:B2,0)),MAX(A$2:A2)+1,INDEX(A$2:A2,MATCH(B3,B$2:B2,0))) in A3.
Fill down the formula in A3.

answered Oct 13, 2011 at 3:34

Excellll

5,8354 gold badges41 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1