Count a Cell Range Unique 3

[roadstain]

I have a column in an Excel Doc. I have the column as a named cell range and I would like to know how to count the values in the range unique so that if the column had below(as an example.)

One
One
Two
Three

would return the value '3' not '4' as I only want to count the unique instances in the named cell range. I would rather do this as a function in another cell but would be willing to do this with a command button launch if I have to.

Any help would be appreciated.

 

[DaleWatson123321]

gritch,

I seem to recall this type of request before, and that someone was able to come up with a "function". So, let's hope that expert is "listening" and will come through.

In the meantime, if you'd like, I can email you a model that you'll probably find useful. I developed it a short time ago for another Tek-Tips member.

The model provides some "basics" related to using VBA (attached to buttons) to:

a) extract UNIQUE records.

b) generate TOTALS for the number of these UNIQUE records - both a COUNT of the number of UNIQUE records, and a SUM of the Dollar Value (another field) related to the UNIQUE records.

c) sorting the extracted records.

There are also "notes" inserted into the file, and these notes deal with a couple "quirks" related to Excel's database functions - i.e. Data - Filter - Advanced Filter.

For example, when attempting to use Data - Filter - Advanced Filter to extract records to a SEPARATE sheet, Excel produces an ERROR message saying that it's NOT possible. This is a "bug" - as it IS possible using the same VBA code that one can record using Data - Filter - Advanced Filter.

Another ERROR message can be encountered that says you HAVE to keep your "criteria" on the SAME sheet as your database. NOT so - it IS possible to have the criteria on a SEPARATE sheet.

If you feel this file would be useful, just email me at my HOME address, and I'll send the file via return email.

As always, if ANYONE ELSE feels this file might be useful, please don't hesitate to ask for it.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[xlbo]

No function needed - just an array formula
=SUM(IF(COUNTIF(a1:a15,a1:a15)=1,1,1/COUNTIF(a1:a15,a1:a15)))
Enter as an array formula ie use ctrl + shift + enter instead of enter (you will see { } surrounding the formula if you have done it right)
This is assuming list in A1:A15

Works for text and values
HTH
Geoff

 

[roadstain]

Tried the formula...

=SUM(IF(COUNTIF(Tape_ID,Tape_ID)=1,1,1/COUNTIF(TapeID,Tape_ID)))

entered and returned an error (#DIV/0!) so I tried this and it worked (did not return an error) but the math is wrong.

=SUM(IF(COUNTIF(Tape_ID,Tape_ID)=1,1/COUNTIF(TapeID,Tape_ID)))

What could I have done wrong here?

 

[roadstain]

Forgot to add that I have an array formula from somewhere else which is largely to complex to run in any reasonable amount of time.

=SUM(IF(FREQUENCY(IF(LEN(Tape_ID)>0,MATCH(Tape_ID,Tape_ID,0), "&quot, IF(LEN(Tape_ID)>0,MATCH(Tape_ID,Tape_ID, 0), "&quot)>0,1))

Each time the named range changed, it would take a good two minutes to set the new value produced by this array fomula - but the math seemed more correct.

 

[Loomah]

As xlbo pointed out, you have to press CTRL+SHIFT+ENTER together to make it an array formula

I wish someone could sit me down and explain these array formulae to me. For some reason I just can't get my head round them! This one, though, I'll be keeping for future ref. Where were you 2 years ago??!!

Happy Friday
;-)

 

[roadstain]

The error did not come from how I entered the array forula. I did use ctrl+ shift+ enter on both instances of the formula supplied above. The main diffrence seems to be the edit I did to the formula. The first formula errored somewhere at the

...1,1,1/COUNTIF...

part which I changed to

...1,1/COUNTIF...

Excel took the formula but the math ended up being wrong.

 

[Loomah]

At the risk of stepping on someones toes, I like this formula and wish I'd had it a couple of years ago.

One thing I have discovered tho' is that it doesn't work if you have any blank cells in the range, hence the #DIV/0!

 

[roadstain]

So I will have to use the other more complex formula to deal with the blank cells. Ok, thanks.

 

[xlbo]

Baddaboom baddabing - works with blanks:
=SUM(IF(COUNTIF(A1:A6,A1:A6)=1,1,IF(ISBLANK(A1:A6),0,1/(COUNTIF((A1:A6),A1:A6)))))

based on target range of A1:A6 - obviously, expand if necessary
and Loomah - 2 years ago, I'd never even heard of an array formula - only really got to uderstand them myself a coupla months ago.
What it does is test various conditions along a range of cells and meltiply the answers (if there is more than one condition - it therefore produces an internal array like:
1*0
1*1
1*0
0*1
1*1
1*1
0*1
this gives a result set of 0,1,0,0,1,1,0
and then either counts or sums the results (dependant on your operator)

obviously, you only get a 1 result where both condition results = 1 as 1*1 is the only result that doesn't produce a 0 - hence the reason they can be used for multiple criteria functions
HTH
Geoff

 

[DaleWatson123321]

Geoff,

My other posting started with... I seem to recall this type of request before, and that someone was able to come up with a "function".

Well, your solution was obviously the one I had been referrring to. In fact, I subsequently "found" the example file I had created.

THANKS !!! - it appears the BEST option for determining the number of UNIQUE items in a field.

======> another STAR.

I would still suggest that IF someone needs to produce a SEPARATE UNIQUE LIST, then Excel's database "extraction" option ( Data - Filter - Advanced Filter ) should be considered.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca