Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Distinct only
Hi Guys, Problem I am trying to resolve is this, I am trying to extract data
from a table of data, I want the result to tell me the number of times a certain piece of info appears ,,, example table Product Code Cost 1 Pen 12345 £2:50 2 Pencil 13456 £1:00 3 Pen 12345 £2:50 4 Pen 12378 £2:50 5 Pen 12378 £2:50 Normmaly I would extract the number of times Pen appears by doing a simple Countif on column A, however, I would like to extract the number of Codes that appear in column B when Pen appears in column A, in the example above the reult would be 4 fo column A, any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Distinct only
Hi John
In your example every occurrence of Pen has a code so a Countif of Pen would yield the same result. If there is not always a code then =SUMPRODUCT(--(A1:A5="Pen"),--(B1:B5<"")) would yield an answer of 4, but a lower value in any codes were blank. Regards Roger Govier John Moore wrote: Hi Guys, Problem I am trying to resolve is this, I am trying to extract data from a table of data, I want the result to tell me the number of times a certain piece of info appears ,,, example table Product Code Cost 1 Pen 12345 £2:50 2 Pencil 13456 £1:00 3 Pen 12345 £2:50 4 Pen 12378 £2:50 5 Pen 12378 £2:50 Normmaly I would extract the number of times Pen appears by doing a simple Countif on column A, however, I would like to extract the number of Codes that appear in column B when Pen appears in column A, in the example above the reult would be 4 fo column A, any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Distinct only
Hi Roger, this wroks to a certain degree, however, is there a way to only
count the number of codes, excluding duplicates, i.e., if a code appears more than once, only count it once? "Roger Govier" wrote: Hi John In your example every occurrence of Pen has a code so a Countif of Pen would yield the same result. If there is not always a code then =SUMPRODUCT(--(A1:A5="Pen"),--(B1:B5<"")) would yield an answer of 4, but a lower value in any codes were blank. Regards Roger Govier John Moore wrote: Hi Guys, Problem I am trying to resolve is this, I am trying to extract data from a table of data, I want the result to tell me the number of times a certain piece of info appears ,,, example table Product Code Cost 1 Pen 12345 £2:50 2 Pencil 13456 £1:00 3 Pen 12345 £2:50 4 Pen 12378 £2:50 5 Pen 12378 £2:50 Normmaly I would extract the number of times Pen appears by doing a simple Countif on column A, however, I would like to extract the number of Codes that appear in column B when Pen appears in column A, in the example above the reult would be 4 fo column A, any ideas? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Distinct only
John,
Try =SUM(--(FREQUENCY(IF(A2:A100="Pen",MATCH(B2:B100,B2:B100, 0)),ROW(INDIRECT("1 :"&ROWS(B2:B100))))0)) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "John Moore" wrote in message ... Hi Roger, this wroks to a certain degree, however, is there a way to only count the number of codes, excluding duplicates, i.e., if a code appears more than once, only count it once? "Roger Govier" wrote: Hi John In your example every occurrence of Pen has a code so a Countif of Pen would yield the same result. If there is not always a code then =SUMPRODUCT(--(A1:A5="Pen"),--(B1:B5<"")) would yield an answer of 4, but a lower value in any codes were blank. Regards Roger Govier John Moore wrote: Hi Guys, Problem I am trying to resolve is this, I am trying to extract data from a table of data, I want the result to tell me the number of times a certain piece of info appears ,,, example table Product Code Cost 1 Pen 12345 £2:50 2 Pencil 13456 £1:00 3 Pen 12345 £2:50 4 Pen 12378 £2:50 5 Pen 12378 £2:50 Normmaly I would extract the number of times Pen appears by doing a simple Countif on column A, however, I would like to extract the number of Codes that appear in column B when Pen appears in column A, in the example above the reult would be 4 fo column A, any ideas? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Distinct only
Consider creating a helper column formed by concatinating Product and Code.
=A1 & B1 This would reduce your problem back to one dimension and you could still use you countif technique. -- Gary's Student "John Moore" wrote: Hi Guys, Problem I am trying to resolve is this, I am trying to extract data from a table of data, I want the result to tell me the number of times a certain piece of info appears ,,, example table Product Code Cost 1 Pen 12345 £2:50 2 Pencil 13456 £1:00 3 Pen 12345 £2:50 4 Pen 12378 £2:50 5 Pen 12378 £2:50 Normmaly I would extract the number of times Pen appears by doing a simple Countif on column A, however, I would like to extract the number of Codes that appear in column B when Pen appears in column A, in the example above the reult would be 4 fo column A, any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Distinct Values? | Excel Worksheet Functions | |||
count distinct in Pivot table | Excel Discussion (Misc queries) | |||
Count Distinct Values by Group Using Pivot Table (NM) | Excel Worksheet Functions | |||
how can I count distinct names in an excel list? | Excel Discussion (Misc queries) | |||
Count distinct | Excel Worksheet Functions |