?
Solved

Excel 2000 - Count values in column

Posted on 2011-02-17
18
Medium Priority
?
279 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 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 500 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 500 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 500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

752 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