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.

Try this:

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

--
Biff
Microsoft Excel MVP


"Joe" <(E-Mail Removed)> wrote in message
news:6DF7BD07-BBC0-46C0-84B6-(E-Mail Removed)...
>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.

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" <(E-Mail Removed)> wrote in message
> news:6DF7BD07-BBC0-46C0-84B6-(E-Mail Removed)...
> >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.
>
>
>

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" <(E-Mail Removed)> wrote in message
news:2C637522-F66F-45D3-B6EC-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
>> news:6DF7BD07-BBC0-46C0-84B6-(E-Mail Removed)...
>> >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.
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:2C637522-F66F-45D3-B6EC-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
> >> news:6DF7BD07-BBC0-46C0-84B6-(E-Mail Removed)...
> >> >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.
> >>
> >>
> >>
>
>
>

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.

--
Biff
Microsoft Excel MVP


"Joe" <(E-Mail Removed)> wrote in message
news:BC0006AD-332D-4249-98CF-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
>> news:2C637522-F66F-45D3-B6EC-(E-Mail Removed)...
>> >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" <(E-Mail Removed)> wrote in message
>> >> news:6DF7BD07-BBC0-46C0-84B6-(E-Mail Removed)...
>> >> >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.
>> >>
>> >>
>> >>
>>
>>
>>

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,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.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Joe" <(E-Mail Removed)> wrote in message
> news:BC0006AD-332D-4249-98CF-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
> >> news:2C637522-F66F-45D3-B6EC-(E-Mail Removed)...
> >> >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" <(E-Mail Removed)> wrote in message
> >> >> news:6DF7BD07-BBC0-46C0-84B6-(E-Mail Removed)...
> >> >> >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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Hi,

You can also use pivot tables.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Joe" <(E-Mail Removed)> wrote in message
news:6DF7BD07-BBC0-46C0-84B6-(E-Mail Removed)...
> 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.

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Joe" <(E-Mail Removed)> wrote in message
news:562E2BB3-4EC8-4239-9E89-(E-Mail Removed)...
> 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,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.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Joe" <(E-Mail Removed)> wrote in message
>> news:BC0006AD-332D-4249-98CF-(E-Mail Removed)...
>> >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" <(E-Mail Removed)> wrote in message
>> >> news:2C637522-F66F-45D3-B6EC-(E-Mail Removed)...
>> >> >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" <(E-Mail Removed)> wrote in message
>> >> >> news:6DF7BD07-BBC0-46C0-84B6-(E-Mail Removed)...
>> >> >> >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.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>