Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

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.
0
wrt1mea
Asked:
wrt1mea
  • 3
1 Solution
 
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
 
leptonkaCommented:
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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now