Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

I need an excel formula

Posted on 2013-01-10
5
Medium Priority
?
388 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

721 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