• Status: Solved
• Priority: Medium
• Security: Public
• Views: 197

# need help to write a formula to count number

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
jjxia2001
1 Solution

Commented:
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

Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.