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
--
Kind regards

Ann Shaw

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

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

Regards,
Stefi

€žAnnie€� ezt Ã*rta:

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
--
Kind regards

Ann Shaw

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


On Jan 30, 1:09*pm, Annie wrote:
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
--
Kind regards

Ann Shaw

Say the list is in column H:

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

adjust the range to suit your needs.
--
Gary''s Student - gsnu200829


"Annie" wrote:

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
--
Kind regards

Ann Shaw

Hi,

You can try this formula

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Annie" wrote in message
...
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
--
Kind regards

Ann Shaw

Excellent!

Thanks so much...
--
Kind regards

Ann Shaw


"Stefi" wrote:

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

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

Regards,
Stefi

€žAnnie€� ezt Ã*rta:

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
--
Kind regards

Ann Shaw

You are welcome! Thanks for the feedback!
Stefi

€žAnnie€� ezt Ã*rta:

Excellent!

Thanks so much...
--
Kind regards

Ann Shaw


"Stefi" wrote:

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

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

Regards,
Stefi

€žAnnie€� ezt Ã*rta:

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
--
Kind regards

Ann Shaw

Please follow following steps
1.Right Click toolbarclick 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 paste
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 in
column B

Have a nice time….

Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com




--
Chris Bode