Solved

Excel 2000 - Count values in column

Posted on 2011-02-17
18
264 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

867 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now