# Excel 2000 - Count values in column

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
LVL 1
###### Who is Participating?

Commented:
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

Commented:
=SUMPRODUCT( 1/COUNTIF(A1:A10;A1:A10))
0

Commented:
The formula only works if you don't have blank cells!
0

IT consultantAuthor Commented:
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

Commented:
If you have blanks, you need to use this formula:

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

jppinto
0

Commented:
Please check the attached working sample.

jppinto
CountCategories-1-.xls
0

Commented:
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

Commented:
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
End If
Next celle

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

End Sub
``````
Copy-of-CountCategories-01.xls
0

IT consultantAuthor Commented:

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

Commented:
Did you array enter it with Ctrl+Shift+Enter?
0

Commented:
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
Next celle

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

End Sub
``````
Copy-of-CountCategories-02.xls
0

Commented:
If you want to count with blanks, please check the attached file.
CountCategories-1-.xls
0

IT consultantAuthor Commented:
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

Commented:
Because it is an array formula and they have to be entered that way.
0

Commented:
Did you checked the attached file on my last post?
0

IT consultantAuthor Commented:
Thanks for everyone, Patrick also your code works
0

IT consultantAuthor Commented:
Yes Jppinto that one also works, so I split the points as 125 for everyone
0

Commented:
csehz,

Of course it does!

Thanks for the points.

Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.