?
Solved

need help to write a formula to count number

Posted on 2011-09-07
2
Medium Priority
?
190 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 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

770 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