Solved

# Excel 2000 - Count values in column

Posted on 2011-02-17
263 Views
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
Question by:csehz
• 6
• 5
• 3
• +2

LVL 33

Expert Comment

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

LVL 33

Expert Comment

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

LVL 1

Author Comment

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

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

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

ID: 34915933
Please check the attached working sample.

jppinto
CountCategories-1-.xls
0

LVL 50

Assisted Solution

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

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

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

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

LVL 1

Author Comment

ID: 34916020

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

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

LVL 45

Expert Comment

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

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

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

LVL 33

Assisted Solution

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

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

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

LVL 33

Expert Comment

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

LVL 1

Author Closing Comment

ID: 34916167
Thanks for everyone, Patrick also your code works
0

LVL 1

Author Comment

ID: 34916177
Yes Jppinto that one also works, so I split the points as 125 for everyone
0

LVL 45

Expert Comment

ID: 34916200
csehz,

Of course it does!

Thanks for the points.

Patrick
0

## Featured Post

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.