Solved

Excel 2000 - Count values in column

Posted on 2011-02-17
18
270 Views
Last Modified: 2012-05-11
Dear Experts,

Could you please advise is there some function in Excel or in VBA, which can count how many different items are in a column?

So for example in the attached file in column A are values, that function would give back value 6.

Because the values are "A", "B", "C", blank, 1, "Text", independently that some of them is multiple times

thanks,
CountCategories.xls
0
Comment
Question by:csehz
  • 6
  • 5
  • 3
  • +2
18 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 34915869
=SUMPRODUCT( 1/COUNTIF(A1:A10;A1:A10))
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34915910
The formula only works if you don't have blank cells!
0
 
LVL 1

Author Comment

by:csehz
ID: 34915915
Thanks, basically works but because of the blank field in A8, I got #DIV/0! as result of =SUMPRODUCT( 1/COUNTIF(A1:A10;A1:A10)).

Do you have idea which could count also the blank?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 125 total points
ID: 34915917
Array-enter:

=SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""), IF(LEN(A1:A10)>0,MATCH(A1:A10,A1:A10,0),""))>0,1))+(COUNTIF(A1:A10,"")>0)

0
 
LVL 33

Expert Comment

by:jppinto
ID: 34915923
If you have blanks, you need to use this formula:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

jppinto
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34915933
Please check the attached working sample.

jppinto
CountCategories-1-.xls
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 125 total points
ID: 34915939
Assuming blanks should be counted (i.e. to get 6 for that sample) you need

=SUMPRODUCT( 1/COUNTIF(A1:A10,A1:A10&""))

regards, barry
0
 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 125 total points
ID: 34916008
csehz,

In the atteched file, press the button on Sheet1 to find out how many differnt items there are in column B. VBA code below.

Patrick
Sub uniqueitems()
Dim rng As Range
Dim celle As Range
Dim coll As New Collection

With Sheets("Sheet1")
    Set rng = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each celle In rng
    If celle <> "" Then
        On Error Resume Next
        coll.Add CStr(celle), CStr(celle)
    End If
Next celle

MsgBox "There are " & coll.Count & " different items in column B"

End Sub

Open in new window

Copy-of-CountCategories-01.xls
0
 
LVL 1

Author Comment

by:csehz
ID: 34916020
Thanks the answers,

Jppinto opening your excel file, that shows value 5 for me in E1. So seems that the formula still did not count the blank,

Rory, unfortunately in my Excel the formulas needs the ";" instead of ",", so I need to replace them like below. But I assume I did wrongly or something because this gives value 2

=SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0;MATCH(A1:A10;A1:A10;0);""); IF(LEN(A1:A10)>0;MATCH(A1:A10;A1:A10;0);""))>0;1))+(COUNTIF(A1:A10;"")>0)

Barry, your formula works for me bringing 6, thanks.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34916029
Did you array enter it with Ctrl+Shift+Enter?
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34916034
csehz,

If you want to count blanks as a different item then use the code below - it's in the attached file.

Patrick
Sub uniqueitems()
Dim rng As Range
Dim celle As Range
Dim coll As New Collection

With Sheets("Sheet1")
    Set rng = Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each celle In rng
    On Error Resume Next
    coll.Add CStr(celle), CStr(celle)
Next celle

MsgBox "There are " & coll.Count & " different items in column B - including blanks"

End Sub

Open in new window

Copy-of-CountCategories-02.xls
0
 
LVL 33

Assisted Solution

by:jppinto
jppinto earned 125 total points
ID: 34916053
If you want to count with blanks, please check the attached file.
CountCategories-1-.xls
0
 
LVL 1

Author Comment

by:csehz
ID: 34916087
Rory aah when entering the formula, after pressing Ctrl-Shift-Enter, well yes in that way brings 6. Thanks, but I will need to understand this that why finally had to press like this :-)))). But works absolutely
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34916099
Because it is an array formula and they have to be entered that way.
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34916159
Did you checked the attached file on my last post?
0
 
LVL 1

Author Closing Comment

by:csehz
ID: 34916167
Thanks for everyone, Patrick also your code works
0
 
LVL 1

Author Comment

by:csehz
ID: 34916177
Yes Jppinto that one also works, so I split the points as 125 for everyone
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34916200
csehz,

>...Patrick also your code works

Of course it does!

Thanks for the points.

Patrick
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question