?
Solved

Match formula for duplicate values

Posted on 2012-12-28
3
Medium Priority
?
177 Views
Last Modified: 2012-12-28
Hi! Column A1:A25000 has data. This data has duplicates. How do I count total values excluding the repeating numbers? Thanks! P.S. I have Excel 2010.
0
Comment
Question by:Ladkisson
[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 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38727375
Try this array formula

=SUM(IF(FREQUENCY(IF(LEN(A2:A100)>0,MATCH(A2:A100,A2:A100,0),""), IF(LEN(A2:A100)>0,MATCH(A2:A100,A2:A100,0),""))>0,1))
0
 
LVL 7

Accepted Solution

by:
leptonka earned 800 total points
ID: 38727377
Hi!

So you have numbers in column A? And you need how many different numbers you have?
Then you can use this formula:

=SUM(--(FREQUENCY(A1:A25000,A1:A25000)>0))
confirm with Ctrl+Shift+Enter.

Cheers,
Kris
0
 

Author Closing Comment

by:Ladkisson
ID: 38727398
Great! Short to the point! Thank you!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

764 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