COUNT UNIQUE TEXT IN COLUMN

Assume your list is in Column A starting in A1
In column B1 enter the formula
=if(iserror(Match(A1,$A$1:A1,0)),1,0)
drag the formula down the list and then sum up the 1's

MALC

I INPUT FORMULA IN COLUMN B AND DRAG DOWN BUT
IT RETURNS ALL ZERO IN COLUMN B

YANECKC

Hi Yaneckc

Try this one and drag it down.
=IF(COUNTIF($A$1:$A$1000,A1)=1,1,0)

A better way though maybe to use a very simple Pivot Table ?

Dave

OzGrid Business Applications

DAVE
WHEN I INPUT THAT FORMULA IT DOES NOT COUNT
CAT AND FISH.
WHAT I WANT TO DO IS I HAVE A LARGE SPREADSHEET WITH TEXT IN COLUMN A. I WANT COUNT THE TEXT IN COLUMN A. IF THERE IS A DUPLICATE OR MORE COUNT IT ONE TIME. MEANING IF CAT IS LIST TEN TIMES ONLY COUNT CAT ONCE.

YANECKC

At the risk of attracting anyone's wrath for suggesting an array formula, this array formula (enter with Ctrl+Shift+Enter) should work :-
=SUM(1/COUNTIF(A1:A1000,A1:A1000))

Ok, gotcha now!
Leave the formula in cell B1 and in B2 put this:

=IF(AND(COUNTIF($A$1:$A$1000,A2)>1,COUNTIF($A$1:A1,A2)=0),1,0)

Copy this down as far as needed.

Dave

OzGrid Business Applications

Oops...

I may have misread you again :o)

Somebody, I'm not too sure who, has proposed an array that will do it. I do know it wasn't Mark W..... or Aladin though, as they have no idea when to use arrays and when not to (hence my warning about arrays on my Website). In this case an array is a good idea.

You could also get a list of all unique entries in your list by using the Advanced filter set to "Unique Value Only" let me know if interested.

Davwe
OzGrid Business Applications

But........

The formula I suggested will only work on the range A1:A1000 if all cells are not blank. This one will be better - the data does not have to populate all cells in A1:A1000.
=SUM(IF(COUNTIF(A1:A1000,A1:A1000)=0,"",1/COUNTIF(A1:A1000,A1:A1000)))

Re: But........

THANK YOU
THAT WAS EXACTLY WHAT I WAS LOOKING FOR.
YANECKC

Re: But........

Hmmm must be Easter Monday or Sunday whereever you are.
Anyway
So why didn't the match thing work?

Re: But........

Malc

The last 2 args of IF had to be switched for it to work. An unfortunate accident.

Aladin

Dishing out again...

WHY DIDN'T YOU PROPOSE ONE YOURSELF THEN? YOU DIDN'T HAVE TIME TO RUSH TO YOUR WEBSITE, I SUPPOSE.

Oooohh! aren't we getting touchy

WHY DIDN'T YOU PROPOSE ONE YOURSELF THEN? YOU DIDN'T HAVE TIME TO RUSH TO YOUR WEBSITE, I SUPPOSE.

Don't worry, you bum chum will be back soon to support you.

BIG kiss Dave XXXXXXXX

Re: But........

The last 2 args of IF had to be switched for it to work. An unfortunate accident. Aladin

But won't that just return all 1's instead of all 0's ?

Syntax

Annie

You're absolutely right. It wouldn't solve YANECKC's problem. I was just concerned with the syntax & I should have said so.

To make it solve the problem, we can do the following:

Assuming the words in A from A1 on:

in B1 enter: =IF(LEN(A1)>0,IF(ISNUMBER(MATCH(A1,$A$1:A1,0)),1,0),"")

in B2 enter: =IF(LEN(A2),IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),0,1),"") [ Copy down this as far as needed ]

in C1 enter: =SUM(B:B)

You can also use VLOOKUP instead of MATCH:

in B1 enter: =IF(LEN(A1)>0,IF(ISNA(VLOOKUP(A1,$A$1:A1,0)),0,1),"")

in B2 enter: =IF(LEN(A2)>0,IF(ISNA(VLOOKUP(A2,$A$1:A1,1,0)),1,0),"") [ Copy down this as far as needed ]

in C1 enter: =SUM(B:B)

However, I'd rather prefer using an array-formula in this case.

Why don't Dave, Mark W and Aladin meet up for a fight?

Isn't this inappropriate behaviour on a website made for giving out advice to users of Excel? If Dave is attempting to promote his business then he's not doing himself any favours. I for one would not approach him on a professional basis if he refers to other knowledgeale and helpful contributors to this site as "bum chums". Also why doesn't Mark W just fuck off somewhere else?

Re: Why don't Dave, Mark W and Aladin meet up for a fight?

Your dead right, I'm too easily baited at times! You have made a good observation. I will ignore them from now on.

Dave
OzGrid Business Applications

Pardon me for butting in, but another question along similar lines....

I want to count the number of times specific text values appear in a column. Ok I can do this with a column per text value searched for down the side of the column I am querying, but it's not very neat and should I want to extend the list I have to also extend the extra columns.

Is there a function that will just allow me to enter the range and the value sought and return a numeric total? I've been trying to make sense of DCOUNTA etc. but with no luck - am I off track? What is the simple answer?? Help!

I want to count the number of times specific text values appear in a column. Ok I can do this with a column per text value searched for down the side of the column I am querying, but it's not very neat and should I want to extend the list I have to also extend the extra columns. Is there a function that will just allow me to enter the range and the value sought and return a numeric total? I've been trying to make sense of DCOUNTA etc. but with no luck - am I off track? What is the simple answer?? Help!

=COUNTIF(A1:A1000,"text")

Re: Why don't Dave, Mark W and Aladin meet up for a fight?

I don't think that ignoring other contributors would be a good idea. Just look backwards in posts and you will find many answers that were solved only after everybody's answer. Maybe if all of you try to improve each other's answers instead of catchin the errors and laugh at it, the help that we need would be more accurate and more appreciated. Well, it's only an opinion.

I like Mark and Dave's contributions. I've learned from both of them.

I would not want to see anyone leave this board. I have learned a great deal from Mark. I look forward to reading his responses, and then trying his suggestions.

I've also learned a lot from Dave, and visited his web site.

I check the message board daily, and always look for responses posted by Mark and Dave. I know that by reading their responses, there is a good chance I am going to learn something new.

Keep posting Mark and Dave. I appreciate you guys.

Re: Syntax

You're absolutely right. It wouldn't solve YANECKC's problem. I was just concerned with the syntax & I should have said so. To make it solve the problem, we can do the following: Assuming the words in A from A1 on: in B1 enter: =IF(LEN(A1)>0,IF(ISNUMBER(MATCH(A1,$A$1:A1,0)),1,0),"") in B2 enter: =IF(LEN(A2),IF(ISNUMBER(MATCH(A2,$A$1:A1,0)),0,1),"") [ Copy down this as far as needed ] in C1 enter: =SUM(B:B) You can also use VLOOKUP instead of MATCH: in B1 enter: =IF(LEN(A1)>0,IF(ISNA(VLOOKUP(A1,$A$1:A1,0)),0,1),"") in B2 enter: =IF(LEN(A2)>0,IF(ISNA(VLOOKUP(A2,$A$1:A1,1,0)),1,0),"") [ Copy down this as far as needed ] in C1 enter: =SUM(B:B) However, I'd rather prefer using an array-formula in this case.

Not a syntax problem or need a complicated formula just needed me to go work and try it out.In B2 enter the formula. B1 is of course 1
=IF(ISNA(MATCH(A2,$A$1:A1,0)),1,0)
and drag it down then sum up the ones

Or use A. R. OK's single cell array formula !

Re: Syntax

=============================

Malc

You have now almost the same formulation with MATCH that I put forward in response to Annie.

You use ISNA where I use ISNUMBER. They are equivalent in effect. My preference is towards ISNUMBER in that it reminds me of the fact that MATCH returns (if successful) a number indicating the location of the item in a one-dimensional list (a vector or an array). You appear to think that my wrapping of MATCH into an IF in order to check length of the items to be evaluated results in a complicated system of formulas. But, that is needed. Also Otherwise, we'll count a blank as a distinct word. If that's desired (I don't believe that it is), you can simply drop the IF around my version.

My observation about the syntax wasn't incorrect (and not meant as offense), because I believed you were hinting at a solution similar to ones we are now discussing about.

Regards.

Aladin