count of unique values within a column

[Guest]

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.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Roger Govier]

Hi Jason

As an alternative to Sandy's suggestion, you could use
Select column E:F>Data>Consolidate>Function Count>Source>A:B>Use labels
in Left Column>OK

After data changes. just choose Data>Consolidate>OK

 

[Guest]

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

 

[Guest]

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.

 

[T. Valko]

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.

 

[Guest]

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.

 

[Roger Govier]

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 data>Data>Pivot Table>Finish
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.