MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Count how many values in a column only appear once??


Posted by Danny on October 26, 2001 4:51 PM

Is this possible??
Lets say I have work order numbers in one column , and I would like to know how many of my work orders only appear once?? Is there a formula to acheive this??
Thanks for any help??
Danny


Posted by Dave Patton on October 26, 2001 8:14 PM

Try =SUM(N(FREQUENCY(rng,rng)=1)) Name your Range rng or replace the 2 rng with cell references

Posted by Danny on October 26, 2001 10:06 PM

I tried that formula and replaced rng,rng with A1,A1000 but unfortunately it just results in a '1' in the cell with the formula in it??
Thanks
Danny

Posted by Aladin Akyurek on October 26, 2001 10:38 PM

The formula should work. Care to post 5 order numbers that you have?

Posted by Danny on October 27, 2001 5:00 PM

They are actually customer drawing numbers and typically column A would look like this...

14635
13756
12845
15965
13524
13756
Now if I had a formula that worked it would tell me that 4 drawing numbers from this list only appeared once ( unique ). The formula that I tried always said 1 as a result, no matter what I typed in column A.
Thanks again guys...
Danny

Posted by Dave Patton on October 27, 2001 7:20 PM

The formula works fine.

Name your data range rng or edit the formula
and include the relevant range which is the full range Both times.

=SUM(N(FREQUENCY(A1:A6,A1:A6)=1))

Please try the formula before saying that it does not work!

Posted by Aladin Akyurek on October 28, 2001 12:34 AM

Danny,

The formula Dave Patton suggested produces 4 when applied to the sample set as it should. If you entered the formula correctly, [ that is,
it should look like: =SUM(N(FREQUENCY(A1:A6,A1:A6)=1)) ], you shouldn't get 1. Be aware that the formula works on a range of numeric data type.

You say that these numbers "are actually customer drawing numbers". How do you produce them? By a formula?

Aladin

=========== They are actually customer drawing numbers and typically column A would look like this... 14635

Posted by Danny on October 28, 2001 12:49 PM

I am so sorry to you both , I didn't have type formula correctly, I had =SUM(N(FREQUENCY(A1,A6)=1)) not =SUM(N(FREQUENCY(A1:A6,A1:A6)=1)
Thankyou so much for your effort.
Danny Danny, The formula Dave Patton suggested produces 4 when applied to the sample set as it should. If you entered the formula correctly, [ that is,