COUNTA Function

[sgl]

I have the following list. Column A has the below formula and column B
contains a list of Names

ColumnA Column B
1 ABCD
2 EFGH
3 IJKL
4 MNOP
"" MNOP
5 XYZ

I am using the following in Column A =If(B1="","",counta(B$1:$B1)). This is
copied all the way down Column B. What I need to achieve is that the formula
in Column A will increment only for Unique Values of Column B. Therefore when
we get to Rows 4 and 5 in Column B we only count the name MNOP only once. Row
6 for Name XYZ will show "5" and NOT "6" as I am getting now

Thank you all/sgl

 

[Mike H]

Hi,

Enter a 1 in A1 because that has to be unique then put this in A2 and drag
down as far as required

=IF(B2<>B1,MAX($A$1:A1)+1,"")

Mike

 

[sgl]

Mike thanks for your quick response.

The formula works well but I had not posted one possibility, that in Column
B there may be consecutive Blank Rows and therefore your suggestion counts
blank cells since they are identical. In my initial posting if Rows 6 and 7
are Blank, then Row 7 would be counted. I only need to count cells that have
values in them.

Thanks/sgl

 

[Mike H]

Slight change required

=IF(AND(B2<>B1,B2<>""),MAX($A$1:A1)+1,"")

Mike

 

[sgl]

Mike,
Worked very well.Your asistance greatly appreciated. Thank you.
sgl