Solved

need help to write a formula to count number

Posted on 2011-09-07
2
188 Views
Last Modified: 2012-05-12
I have a column with numbers as below and I want to write a formula in excel cells to count how many for each number (how many 1, how many 2...).  Can you help me?


1
2
2
3
4
4
5
.
.
.
0
Comment
Question by:jjxia2001
[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
2 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 250 total points
ID: 36498339
to count 1s for instance you can use COUNTIF, e.g. for column A

=COUNTIF(A:A,1)

if you have many numbers to count then try listing the numbers in C2 down and then use this version in D2 copied down the column

=COUNTIF(A:A,C2)

regards, barry
0
 
LVL 6

Expert Comment

by:theKashyap
ID: 36498447
I donno if there is any formula as such to do sort unique (remove duplicates). So you can do it manually or using macro. Once you have a unique list then for each number in unique list you can use COUNTIF function to count number of occurrences of that number in the original list.

To manually remove duplicates, here is the help from excel (Excel > Filtering, sorting, and conditionally formatting data > Filter for unique values or remove duplicate values):

Remove duplicate values
When you remove duplicate values, only the values in the range of cells or table are affected. Any other values outside the range of cells or table are not altered or moved.

 Caution    Because you are permanently deleting data, it's a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values.

Select the range of cells, or make sure that the active cell is in a table.
On the Data tab, in the Data Tools group, click Remove Duplicates.


Do one or more of the following:
Under Columns, select one or more columns.
To quickly select all columns, click Select All.
To quickly clear all columns, click Unselect All.
If the range of cells or table contains many columns and you want to only select a few columns, you may find it easier to click Unselect All, and then under Columns, select those columns.
Click OK.
A message is displayed indicating how many duplicate values were removed and how many unique values remain, or if no duplicate values were removed.

Click OK.
Issue: I'm having problems removing duplicate values from data that is outlined or that has subtotals.

You cannot remove duplicate values from data that is outlined or that has subtotals. To remove duplicates, you must remove both the outline and the subtotals. For more information, see Outline a list of data in a worksheet and Remove subtotals.
 Top of Page

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 tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

726 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