Count
in [General]
|
From: MFS on 18 Apr 2010 15:08 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 are: 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
From: Billy Liddel on 18 Apr 2010 15:28 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 are: > 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
From: Bernard Liengme on 18 Apr 2010 15:34 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" <BillyLiddel(a)discussions.microsoft.com> wrote in message news:63CD7D0F-DC1D-4F82-A9AF-ACC324A9CD10(a)microsoft.com... > 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 are: >> 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
From: T. Valko on 18 Apr 2010 16:59 >=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" <BillyLiddel(a)discussions.microsoft.com> wrote in message news:63CD7D0F-DC1D-4F82-A9AF-ACC324A9CD10(a)microsoft.com... > 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 are: >> 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
|
Pages: 1 Prev: UTF-8 Delimited Next: VBA selecting an input range of cells |