I need an excel formula

I need an excel formula to return the number of unique values in Column A

Then I need another formula to return a number of unique values in column A , only if Column K = N.
LVL 1
wrt1meaAsked:
Who is Participating?
 
leptonkaConnect With a Mentor Commented:
The first formula:
=SUM(--(FREQUENCY(A1:A5000,A1:A5000)>0))
(confirm with ctrl+shift+enter)

The second - I am not sure I understand correctly. Values of column K should be "N" - so:
=SUM(--(FREQUENCY(IF(K1:K5000="N",A1:A5000,""),IF(K1:K5000="N",A1:A5000,""))>0))
(confirm with ctrl+shift+enter)
or:
Values of column K should be equal to column N? Than:
=SUM(--(FREQUENCY(IF(K1:K5000=N1:N5000,A1:A5000,""),IF(K1:K5000=N1:N5000,A1:A5000,""))>0))
(confirm with ctrl+shift+enter)
In this second case - do you have empty cells in column K and N?

Cheers,
Kris
0
 
SteveCommented:
The following formula Entered with Ctrl+Shift+Enter will count unique values in Column A between A1 and A50000 (cannot use A:A for the array formula)

=SUM(IF(FREQUENCY(IF(LEN(A1:A50000)>0,MATCH(A1:A50000,A1:A50000,0),""), IF(LEN(A1:A50000)>0,MATCH(A1:A50000,A1:A50000,0),""))>0,1))

Am am not sure what you mean by the second part of the question?
0
 
wrt1meaAuthor Commented:
For the second part, i need to return a total number of unique values for column A ONLY IF the values in column K are N
0
 
wrt1meaAuthor Commented:
Three wont be any empty cells in column K, whis is where the letter N (for No) is at.

The values between column k or N do not need to equal so I will try
=SUM(--(FREQUENCY(IF(K1:K5000="N",A1:A5000,""),IF(K1:K5000="N",A1:A5000,""))>0))
(confirm with ctrl+shift+enter)
0
 
wrt1meaAuthor Commented:
Works great!
0
All Courses

From novice to tech pro — start learning today.