Count

[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

 

[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

 

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

 

[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.

 

[Ashish Mathur]

Hi,

You can try this formula

SUMPRODUCT(1/COUNTIF(D411,D411)). Please adjust the range as per your
needs

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Annie]

Excellent!

Thanks so much...

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„Annie†ezt írta:

 

[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