• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

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
0
csehz
Asked:
csehz
  • 6
  • 5
  • 3
  • +2
4 Solutions
 
jppintoCommented:
=SUMPRODUCT( 1/COUNTIF(A1:A10;A1:A10))
0
 
jppintoCommented:
The formula only works if you don't have blank cells!
0
 
csehzAuthor 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
Industry Leaders: 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!

 
Rory ArchibaldCommented:
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
 
jppintoCommented:
If you have blanks, you need to use this formula:

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

jppinto
0
 
jppintoCommented:
Please check the attached working sample.

jppinto
CountCategories-1-.xls
0
 
barry houdiniCommented:
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
 
patrickabCommented:
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
 
csehzAuthor Commented:
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
 
Rory ArchibaldCommented:
Did you array enter it with Ctrl+Shift+Enter?
0
 
patrickabCommented:
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
 
jppintoCommented:
If you want to count with blanks, please check the attached file.
CountCategories-1-.xls
0
 
csehzAuthor 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
 
Rory ArchibaldCommented:
Because it is an array formula and they have to be entered that way.
0
 
jppintoCommented:
Did you checked the attached file on my last post?
0
 
csehzAuthor Commented:
Thanks for everyone, Patrick also your code works
0
 
csehzAuthor Commented:
Yes Jppinto that one also works, so I split the points as 125 for everyone
0
 
patrickabCommented:
csehz,

>...Patrick also your code works

Of course it does!

Thanks for the points.

Patrick
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now