Counting Range

[makinmomb]

I have say as follows on column a with a4 as blank , I want formula to count column a where i need the answer to be 5
that is first cell count to last cell with text

a
b
c

d

[AliGW]

Try again to explain what you want. You have listed four text items (a,b,c,and d) with a blank between c and d. If I were to count the text items, there would be 4 - how do you get 5?

[Pete_UK]

Try this, e.g. in C1:

=MATCH(LOOKUP("zzz",A:A),A:A,0)

Hope this helps.

Pete

[makinmomb]

Thank you Pete_Uk , seems you got me straight without further questions

[Pete_UK]

It will only work properly if the text values are all unique.

Pete

[JohnTopley]

It will only work properly if the text values are all unique.

really ?????

[AliGW]

It will only work if the last item is unique because the MATCH function returns the row of the first instance of that item found.

[JohnTopley]

Yes but chances of it being "ZZZ" ? Better use "ZZZZZZZZ" !

It isn't really a question of uniqueness but of relative values.

[AlKey]

It will only work if the last item is unique because the MATCH function returns the row of the first instance of that item found.

No. =MATCH("zzz",A:A) returns row() of the last cell populated with text. It doesn't care about any duplicates.

[AliGW]

No, John. Consider a list like this:

a
b
c

d
c
a

c

Which row number will be returned? 3, 6 or 9? We want 9, but it will return 6 becasue that is the first row it appears in.

=MATCH(LOOKUP("zzz",A:A),A:A,0) gives 3

=LOOKUP(2,1/(A:A<>""),A:A) gives c

[AliGW]

No. =MATCH("zzz",A:A) returns row() of the last cell populated with text. It doesn't care about any duplicates.

OK - so it's the LOOKUP bit that's causing it.

[JohnTopley]

Me being word blind: I had used MATCH as per Alkey's response: hence my query!

[AliGW]

Yes, there's a subtle difference I hadn't appreciated before now!