Solved

Excel 2000 - Count values in column

Posted on 2011-02-17
18
263 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

707 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

18 Experts available now in Live!

Get 1:1 Help Now