count of unique values within a column
 

not sure if this is possible...I need to get a count of values in a column,
but the column has multiple repeats of the same. it looks something like
this..

account market value
0001 $10,000
0001 $9000
0002 $8000
0002 $12,000

I would need to see that there are 2 instances of account 0001 and 2
instances of account 0002.

count of unique values within a column
 

To count the instances of 001 use:

=COUNTIF(B3:B6,"001")

Where B3:B6 is the list of accounts but I can't help but think that you atre
actually asking for soemthing else.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Jason" wrote in message
...
not sure if this is possible...I need to get a count of values in a
column,
but the column has multiple repeats of the same. it looks something like
this..

account market value
0001 $10,000
0001 $9000
0002 $8000
0002 $12,000

I would need to see that there are 2 instances of account 0001 and 2
instances of account 0002.

count of unique values within a column
 

Hi Jason

As an alternative to Sandy's suggestion, you could use
Select column E:FDataConsolidateFunction CountSourceA:BUse labels
in Left ColumnOK

After data changes. just choose DataConsolidateOK


--
Regards

Roger Govier


"Jason" wrote in message
...
not sure if this is possible...I need to get a count of values in a
column,
but the column has multiple repeats of the same. it looks something
like
this..

account market value
0001 $10,000
0001 $9000
0002 $8000
0002 $12,000

I would need to see that there are 2 instances of account 0001 and 2
instances of account 0002.

count of unique values within a column
 

Data Advanced Filter select Copy to another location List range: select
your range Copy to: select any blank cell click Unique records only OK

After you have all the unique records, then use Countif function


"Jason" wrote:

not sure if this is possible...I need to get a count of values in a column,
but the column has multiple repeats of the same. it looks something like
this..

account market value
0001 $10,000
0001 $9000
0002 $8000
0002 $12,000

I would need to see that there are 2 instances of account 0001 and 2
instances of account 0002.

count of unique values within a column
 

If no blanks in your data, try:
=Sumproduct(1/Countif(A1:A4, A1:A4))

otherwise:
=Sumproduct((A1:A4<"")/Countif(A1:A4,A1:A4&""))

change range reference to match your data.


"Jason" wrote:

not sure if this is possible...I need to get a count of values in a column,
but the column has multiple repeats of the same. it looks something like
this..

account market value
0001 $10,000
0001 $9000
0002 $8000
0002 $12,000

I would need to see that there are 2 instances of account 0001 and 2
instances of account 0002.

count of unique values within a column
 

You might want to tell them that if they want the filtered results on a
different sheet then they must initiate the process from that other sheet.

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Data Advanced Filter select Copy to another location List range:
select
your range Copy to: select any blank cell click Unique records only
OK

After you have all the unique records, then use Countif function


"Jason" wrote:

not sure if this is possible...I need to get a count of values in a
column,
but the column has multiple repeats of the same. it looks something like
this..

account market value
0001 $10,000
0001 $9000
0002 $8000
0002 $12,000

I would need to see that there are 2 instances of account 0001 and 2
instances of account 0002.

count of unique values within a column
 

thanks Sandy,

The reason I can't use a standard count function is that there will
potentially be thousands of account #'s. I would like a formula that looks
at all of the matching account #'s & counts a field based on that.

The data consolidation function sounds like it may work, but I'm not
familiar with that & so far have not had luck.

"Sandy Mann" wrote:

To count the instances of 001 use:

=COUNTIF(B3:B6,"001")

Where B3:B6 is the list of accounts but I can't help but think that you atre
actually asking for soemthing else.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Jason" wrote in message
...
not sure if this is possible...I need to get a count of values in a
column,
but the column has multiple repeats of the same. it looks something like
this..

account market value
0001 $10,000
0001 $9000
0002 $8000
0002 $12,000

I would need to see that there are 2 instances of account 0001 and 2
instances of account 0002.

count of unique values within a column
 

Hi Jason

Sandy's formula using Countif can be applied to whole columns (65536
rows in XL2003 and lower) so that will not be a limitation to its use.
However, if you are saying you don't have a column of unique account
number to use this formula against, as opposed to entering each account
number into a formula, that is a different matter.

Another alternative to the solution I posted for you using Consolidate,
would be to create a Pivot Table
Mark your block of dataDataPivot TableFinish
On the new sheet created with the skeleton Pivot table,
drag Account number from the Filed list to the Row area
drag Account number again to the Data area. If the account numbers are
text, then it will automatically create Count of Account numbers. If it
shows Sum of, then double click on that icon, and select Count.

You will now see a list of all your account numbers with a count of them
alongside.
If you also wanted to know the total of the values associated with each
account, then drag the Value field to the data area as well.
--
Regards

Roger Govier


"Jason" wrote in message
...
thanks Sandy,

The reason I can't use a standard count function is that there will
potentially be thousands of account #'s. I would like a formula that
looks
at all of the matching account #'s & counts a field based on that.

The data consolidation function sounds like it may work, but I'm not
familiar with that & so far have not had luck.

"Sandy Mann" wrote:

To count the instances of 001 use:

=COUNTIF(B3:B6,"001")

Where B3:B6 is the list of accounts but I can't help but think that
you atre
actually asking for soemthing else.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Jason" wrote in message
...
not sure if this is possible...I need to get a count of values in a
column,
but the column has multiple repeats of the same. it looks
something like
this..

account market value
0001 $10,000
0001 $9000
0002 $8000
0002 $12,000

I would need to see that there are 2 instances of account 0001 and
2
instances of account 0002.