how to select distinct values from list

[kisanvikas2015]

Hi all,

Can someone please help me.

I want a formula to select unique values from the list.
I mean if there are values like -1, 5, #REF, 5, 1

Answer should be 2.

Regards,
Vikas

[Glenn Kennedy]

Hi there. try this (assuming data are in a column, staring in A1).

=SUM(IF(FREQUENCY(IF(A1:A5>0,A1:A5),IF(A1:A5>0,A1:A5))>0,1))

This is an Array Formulas. They're a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[kisanvikas2015]

Hi Glenn,

Thanks for the response. I missed exclamation sign after #REF

The formula should ignore any error like #NAME?, #VALUE!, #DIV/0!, #REF!

Regards,
Vikas

[jayajaya_4]

use =iferror(SUM(IF(FREQUENCY(IF(A1:A5>0,A1:A5),IF(A1:A5>0,A1:A5))>0,1)),"")

[Glenn Kennedy]

In your first post - why is the answer 2, not 3?

[Glenn Kennedy]

Did you change the criteria back at Post 1?? I would swear that it originally said unique POSITIVE numbers... For the words as currently there, try this, also entered as an array (It'll give 3 as the answer).

=SUM(IF(1-ISERROR($A$1:$A$5),IF($A$1:$A$5<>"",1))/COUNTIF($A$1:$A$5,$A$1:$A$5&""))

[kisanvikas2015]

In your first post - why is the answer 2, not 3?

@Glenn I want to ignore any kind of error.

Regards,
Vikas

[Glenn Kennedy]

But why is the expected answer 2 and not 3... Minus 1, plus 1 and plus 5 = 3, not 2...

[Tony Valko]

Maybe this...

Data Range

	A	B	C
1	-1		3
2	5
3	#REF
4	5
5	1
6	------	------	------

This array formula** entered in C1:

=SUM(IF(FREQUENCY(IF(ISNUMBER(A1:A5),A1:A5),A1:A5),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[kisanvikas2015]

But why is the expected answer 2 and not 3... Minus 1, plus 1 and plus 5 = 3, not 2...

Hi Glenn,

I want to count unique positive numbers only (ignoring any type of error)

Regards,
Vikas

[Tony Valko]

I want to count unique positive numbers only (ignoring any type of error)

Try this array formula**

=SUM(IF(FREQUENCY(IF(ISNUMBER(A1:A5),IF(A1:A5>0,A1:A5)),A1:A5),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[AlKey]

Try this

=SUMPRODUCT(ISNUMBER(A1:A5)*(A1:A5>0)/COUNTIF(A1:A5,A1:A5))

[Glenn Kennedy]

OK. Why did you REMOVE the criterion of "positive number" from your edit of Post 1. We could have got to the answer FAR more quickly if you had left it in!! I would have suggested this much earlier on:

=SUM(IF(1-ISERROR($A$1:$A$5),IF($A$1:$A$5>0,1))/COUNTIF($A$1:$A$5,$A$1:$A$5&""))

array entered. You (finally) have several working formulae...

[kisanvikas2015]

OK. Why did you REMOVE the criterion of "positive number" from your edit of Post 1. We could have got to the answer FAR more quickly if you had left it in!! I would have suggested this much earlier on:

=SUM(IF(1-ISERROR($A$1:$A$5),IF($A$1:$A$5>0,1))/COUNTIF($A$1:$A$5,$A$1:$A$5&""))

array entered. You (finally) have several working formulae...

Hi Glenn,

Thanks for answer.

Sorry for the confusion.

Thanks to all for helping me.

Regards,
Vikas

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.