count number of unique values in column

[Joe]

I have a column that can have floating values. I want to return in a column
next to it how many of each unique value there is. For example if column A
has three of the number 1 in it and 5 of the number 2 in it I would want the
formula to tell me how many of each there were. I am using this to calculate
how many day to figure interest by the day as deposits and withdrawls are
made.

 

[T. Valko]

Try this:

=COUNTIF(A1:A100,1)
=COUNTIF(A1:A100,2)

 

[Joe]

i put =COUNTIF(A1:A100,1) in column A and I put in column B as shown below
and I don't understand how this would help???? Using what is in column A for
an example, the data in column A can be any number so I can't use the value
in the cell as part of the formula. What I am trying to do is seach column A
and return how many of each unique value. Any ideas?

A B C

1 1 2
2 1 2
2 1 2
3 1 2
3 0 2
3 0 1
4 0 0
4 0 0
4 0 0

 

[T. Valko]

Let's assume you have this data in column A:

A1 = 1
A2 = 1
A3 = 5
A4 = 1
A5 = 2
A6 = 5
A7 = 5

In C1:C3 you have the unique values from column A listed:

C1 = 1
C2 = 2
C3 = 5

Enter this formula in D1 and copy down to D3:

=COUNTIF(A$1:A$7,C1)

 

[Joe]

I see how that works but I don't have the unique values in column C.

All I have is a single column with values in it which is calculating a
running total depending on credits or debits.

The column would start off with, lets say, $10.00 on Feb 1st
It stays at $10.00 each day until $1.00 is deposited on Feb 5th. Now the
running total is $11.00. It will stay $11.00 until another deposit is made or
heaven forbid, a withdrawl.

So, the cells in the column could have row 1 through 5 with a $10.00 in it
and the rest down the column have an $11.00 in them. If I am calculating
interest by the day I need to know how may days the account had $10.00 in it
and how many days it had $11.00 in it. I don't have $10.00 or $11.00 to use
in a formula because these amounts could be anything.

Currently I use the Excel conditional formatting and just manually count
them. It would be nice if a column running beside this one could return how
many unique values were next to it.

Here is what I do manually

A B c

Feb 01 10 ($10.00 is deposited)
Feb 02 10
Feb 03 3 days 10 (this tells me I had bal of $10.00 for 3 days)
Feb 04 11 ($1.00 is depositied)
Feb 05 2 days 11 (this tells me I had bal of $11.00 for 2 days)

So I would pay 4% interest on $10.00 for 3 days and pay 4% interest on
$11.00 for 2 days.

It would be nice to not have to figure out the days manually.

 

[T. Valko]

OK, we can extract the unique numbers and then get the counts for each.

Let's assume your numbers start in cell C2. C1 is your column header. I
imagine it's titled something like Balance.

We'll extract the unique numbers from column C and list them in ascending
order in column E starting in E2. E1 must not be a number that is also found
in column C. You'd probably want cell E1 to be a descriptive column header.

Enter this array formula** in E2:

=IF(ROWS(E$2:E2)<=COUNT(1/FREQUENCY(C$2:C$100,C$2:C$100)),SMALL(C$2:C$100,SUM(COUNTIF(C$2:C$100,E$1:E1))+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy that formula down the column as needed. You would probably want to copy
it to more cells than are currently needed to allow for future dynamic
updates.

Now, enter this formula in F2 to get the counts:

=IF(E2="","",COUNTIF(C$2:C$100,E2)

Copy that formula down as far as you copy the other formula.

 

[Joe]

That did the trick. I am now a happy camper. Thank you Very Much!

 

[Ashish Mathur]

Hi,

You can also use pivot tables.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

Good deal. Thanks for the feedback!