count number of unique values in column
 

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.

count number of unique values in column
 

Try this:

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

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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.

count number of unique values in column
 

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" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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.

count number of unique values in column
 

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)

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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.

count number of unique values in column
 

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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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.

count number of unique values in column
 

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,SU M(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.

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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.

count number of unique values in column
 

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

"T. Valko" wrote:

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,SU M(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.

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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.

count number of unique values in column
 

Hi,

You can also use pivot tables.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Joe" wrote in message
...
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.

count number of unique values in column
 

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
That did the trick. I am now a happy camper. Thank you Very Much!

"T. Valko" wrote:

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,SU M(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.

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Joe" wrote in message
...
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.