Solved

# need help to write a formula to count number

Posted on 2011-09-07
183 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
Question by:jjxia2001
2 Comments

LVL 50

Accepted Solution

barry houdini earned 250 total points
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

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

## Join & Write a Comment Already a member? Login.

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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.

#### 771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!