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?

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?

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?
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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?
> >

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?