Solved

I need an excel formula

Posted on 2013-01-10
5
347 Views
Last Modified: 2013-01-10
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
Comment
Question by:wrt1mea
  • 3
5 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38764679
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
 
LVL 1

Author Comment

by:wrt1mea
ID: 38764697
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
 
LVL 7

Accepted Solution

by:
leptonka earned 500 total points
ID: 38764840
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
 
LVL 1

Author Comment

by:wrt1mea
ID: 38764879
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
 
LVL 1

Author Closing Comment

by:wrt1mea
ID: 38765047
Works great!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now