Count

A

Annie

Hi
I have a column with a list of company names and people working in those
companies - but there are duplicate company names. Can I count the companies
but not the duplicates - there are too many to do countif and I am using 2003
version of Excel. Is there a count non-duplicates function or something like
that?

Thanks
 
P

Pete_UK

Assuming your company names are in A1:A100, this array* formula will
count the unique names:

=SUM(IF(LEN(A1:A100),1/COUNTIF(A1:A100,A1:A100)))

Assumes a data range of A1 to A100 - adjust as necessary.

*An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual Enter. If you do this
correctly then Excel will wrap curly braces around that formula when
viewed in the formula bar - do not type these yourself. If you edit/
amend the formula you need to use CSE again.

Hope this helps.

Pete
 
S

Stefi

In my example the company range is B1:B6, adjust it to your data structure:

=SUMPRODUCT(1/COUNTIF(B1:B6,B1:B6&""))

Regards,
Stefi

„Annie†ezt írta:
 
G

Gary''s Student

Say the list is in column H:

=SUMPRODUCT((H1:H1500<>"")/COUNTIF(H1:H1500,H1:H1500&""))

adjust the range to suit your needs.
 
C

Chris Bode

Please follow following steps
1.Right Click toolbar>click control box
2.From control box select a command button and draw it to your sheet
3.Double click the command button to open code window and past
following codes
Private Sub CommandButton1_Click()
Dim row As Integer, col As Integer, cnt As Integer
row = 1
col = 1
cnt = 1

While Sheet1.Cells(row, col).Value <> ""
cnt = chkRepeat(Sheet1.Cells(row, col).Value, row)

Sheet1.Cells(row, col + 1).Value = cnt
row = row + 1
Wend

End Sub

Private Function chkRepeat(str As String, i As Integer) As Integer
Dim tmp As Integer
tmp = 1

Dim row As Integer, col As Integer
row = 1
col = 1

While Sheet1.Cells(row, col).Value <> ""
If i <> row Then
If Sheet1.Cells(row, col).Value = str Then
tmp = tmp + 1
End If
End If
row = row + 1
Wend

chkRepeat = tmp
End Function

Now on clicking the button you get the number of counts listed i
column B

Have a nice time….

Chris
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top