Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Criteria, Count If, Sum Product to get count across range | Excel Worksheet Functions | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |