Solved

need help to write a formula to count number

Posted on 2011-09-07
2
184 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial will demonstrate how to use a 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.

863 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

20 Experts available now in Live!

Get 1:1 Help Now