If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|