counting unique row values in excel

Question

I have product list like this. I have only product name list

Apple       1
Apple       1
Apple       1
Orange      2
Orange      2
Orange      2
Mango       3
Mango       3
Pineapple   4
Pineapple   4
Pineapple   4
Pineapple   4
Pineapple   4
Pineapple   4
Avocado     5

I want to count data in this way. please help.

Possible duplicate of [Numbering unique values in a worksheet](https://stackoverflow.com/questions/46366816/numbering-unique-values-in-a-worksheet) — dwirony, Nov 07 '17 at 22:18

score 1 · Answer 1 · 2017-11-07T22:21:53.447

1

Put 1 in B1 then put this formula in B2,

=if(a2<>a1, max(b$1:b1)+1, b1)

... and fill down.

Alternately just put his formula into B1,

=SUMPRODUCT(1/(COUNTIF(A$1:A1,A$1:A1)))

... and fill down.

edited Nov 07 '17 at 22:21

answered Nov 07 '17 at 22:14

score 1 · Answer 2 · answered Nov 07 '17 at 22:16

1

In B1 insert 1 and in B2 insert:

=IF(A2=A1,B1,B1+1)

And from there just fill.

answered Nov 07 '17 at 22:16

zipa

27,316
6
40
58

You are welcome :) FYI accepting an answer will increase your reputation. – zipa Nov 08 '17 at 08:49

counting unique row values in excel

2 Answers2