Count
 

Hi,
I would like to know how which formula I should use to count some codes & to
ignore the duplicated codes, for example in column A:
Code
1234
4321
4321
1234
1234
3214

Now, I want Excel to count & the result is 3 which a
1234-4321-3214

Yes I can use PivotTable, but I would like to use a formula because I have
to deal with this every day.

I appreciate your support.

--
MFS22

Count
 

COUNTIF will do it.

=COUNTIF(Rng,Rng), where the criterion is the same as the range:
e.g
=COUNTIF(A3:A8,A3:A8)


HTH
Peter

"MFS" wrote:

Hi,
I would like to know how which formula I should use to count some codes & to
ignore the duplicated codes, for example in column A:
Code
1234
4321
4321
1234
1234
3214

Now, I want Excel to count & the result is 3 which a
1234-4321-3214

Yes I can use PivotTable, but I would like to use a formula because I have
to deal with this every day.

I appreciate your support.

--
MFS22

Count
 

Suppose the data (including the label "Codes") is in A1:A7
Select any cell in that range
Use Data | Advanced Filter, specify where you what the result, check the
Unique box
The thee unique values are list in the specified place.
best wishes
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Billy Liddel" wrote in message
...
COUNTIF will do it.

=COUNTIF(Rng,Rng), where the criterion is the same as the range:
e.g
=COUNTIF(A3:A8,A3:A8)


HTH
Peter

"MFS" wrote:

Hi,
I would like to know how which formula I should use to count some codes &
to
ignore the duplicated codes, for example in column A:
Code
1234
4321
4321
1234
1234
3214

Now, I want Excel to count & the result is 3 which a
1234-4321-3214

Yes I can use PivotTable, but I would like to use a formula because I
have
to deal with this every day.

I appreciate your support.

--
MFS22

Count
 

=COUNTIF(A3:A8,A3:A8)

It's just "dumb luck" if that works. Essentially, this is what the formula
is doing:

=COUNTIF(A3:A8,A3)

It just so happens that there are 3 instances of 1234 in the range and there
are 3 unique values in the range.

Change the entry in cell A3 to abcd and then see what result you get.

The generic formula for counting uniques is:

=SUMPRODUCT((A3:A8<"")/COUNTIF(A3:A8,A3:A8&""))

If the data is numeric as is shown in the OP's sample:

=SUM(--(FREQUENCY(A3:A8,A3:A8)0))

--
Biff
Microsoft Excel MVP


"Billy Liddel" wrote in message
...
COUNTIF will do it.

=COUNTIF(Rng,Rng), where the criterion is the same as the range:
e.g
=COUNTIF(A3:A8,A3:A8)


HTH
Peter

"MFS" wrote:

Hi,
I would like to know how which formula I should use to count some codes &
to
ignore the duplicated codes, for example in column A:
Code
1234
4321
4321
1234
1234
3214

Now, I want Excel to count & the result is 3 which a
1234-4321-3214

Yes I can use PivotTable, but I would like to use a formula because I
have
to deal with this every day.

I appreciate your support.

--
MFS22