Countif unique item with multiple criteria

Hi,

How do I use the Count if or sumproduct formula to count cells with
unique items. The database is organised as follows

Product Year Segment Customer Amt

A 2005 F XA
A 2005 F XB
A 2005 F XA
A 2005 F XC
A 2005 G XA
A 2006 F
B 2005 F
B 2005 G
B 2006 G
B 2006 G


Now if I want to count the number of Unique customers in Product A, for
Year 2005, in Segment F, how is the countif or subproduct formula to be
set up.
By using the formula I should be able to get a count of 3 unique
customers and not 4 for the above criteria.

Appreciate if some one can help on it as it very important.

Regards
Sandip.

Forgot to add it is an array formula, it should be committed with
Ctrl-Shift-Enter, not just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> How do I use the Count if or sumproduct formula to count cells with
> unique items. The database is organised as follows
>
> Product Year Segment Customer Amt
>
> A 2005 F XA
> A 2005 F XB
> A 2005 F XA
> A 2005 F XC
> A 2005 G XA
> A 2006 F
> B 2005 F
> B 2005 G
> B 2006 G
> B 2006 G
>
>
> Now if I want to count the number of Unique customers in Product A, for
> Year 2005, in Segment F, how is the countif or subproduct formula to be
> set up.
> By using the formula I should be able to get a count of 3 unique
> customers and not 4 for the above criteria.
>
> Appreciate if some one can help on it as it very important.
>
> Regards
> Sandip.
>

=SUM(IF(FREQUENCY(IF((A2:A11<>"")*
(B2:B11=2005)*(C2:C11="F"),
MATCH(A2:A11,A2:A11,0)),ROW(INDIRECT("1:"&ROWS(A2:A11))))>0,1))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> How do I use the Count if or sumproduct formula to count cells with
> unique items. The database is organised as follows
>
> Product Year Segment Customer Amt
>
> A 2005 F XA
> A 2005 F XB
> A 2005 F XA
> A 2005 F XC
> A 2005 G XA
> A 2006 F
> B 2005 F
> B 2005 G
> B 2006 G
> B 2006 G
>
>
> Now if I want to count the number of Unique customers in Product A, for
> Year 2005, in Segment F, how is the countif or subproduct formula to be
> set up.
> By using the formula I should be able to get a count of 3 unique
> customers and not 4 for the above criteria.
>
> Appreciate if some one can help on it as it very important.
>
> Regards
> Sandip.
>

Hi Bob,

I tried the formula and it does not seem to give the right total count.
Though I don't fully understand the formula, it is not looking into Col
D which has the list of customers to be counted. Only unique list count
is required.

Regards
Sandip.


Bob Phillips wrote:
> Forgot to add it is an array formula, it should be committed with
> Ctrl-Shift-Enter, not just Enter.
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > How do I use the Count if or sumproduct formula to count cells with
> > unique items. The database is organised as follows
> >
> > Product Year Segment Customer Amt
> >
> > A 2005 F XA
> > A 2005 F XB
> > A 2005 F XA
> > A 2005 F XC
> > A 2005 G XA
> > A 2006 F
> > B 2005 F
> > B 2005 G
> > B 2006 G
> > B 2006 G
> >
> >
> > Now if I want to count the number of Unique customers in Product A, for
> > Year 2005, in Segment F, how is the countif or subproduct formula to be
> > set up.
> > By using the formula I should be able to get a count of 3 unique
> > customers and not 4 for the above criteria.
> >
> > Appreciate if some one can help on it as it very important.
> >
> > Regards
> > Sandip.
> >

Sorry Sandip, mis-read it.

=SUM(IF(FREQUENCY(IF((D2:D11<>"")*
(A2:A11="A")*(B2:B11=2005)*(C2:C11="F"),
MATCH(D2:D11,D2:D11,0)),ROW(INDIRECT("1:"&ROWS(D2:D11))))>0,1))


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> I tried the formula and it does not seem to give the right total count.
> Though I don't fully understand the formula, it is not looking into Col
> D which has the list of customers to be counted. Only unique list count
> is required.
>
> Regards
> Sandip.
>
>
> Bob Phillips wrote:
> > Forgot to add it is an array formula, it should be committed with
> > Ctrl-Shift-Enter, not just Enter.
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (replace xxxx in the email address with gmail if mailing direct)
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi,
> > >
> > > How do I use the Count if or sumproduct formula to count cells with
> > > unique items. The database is organised as follows
> > >
> > > Product Year Segment Customer Amt
> > >
> > > A 2005 F XA
> > > A 2005 F XB
> > > A 2005 F XA
> > > A 2005 F XC
> > > A 2005 G XA
> > > A 2006 F
> > > B 2005 F
> > > B 2005 G
> > > B 2006 G
> > > B 2006 G
> > >
> > >
> > > Now if I want to count the number of Unique customers in Product A,
for
> > > Year 2005, in Segment F, how is the countif or subproduct formula to
be
> > > set up.
> > > By using the formula I should be able to get a count of 3 unique
> > > customers and not 4 for the above criteria.
> > >
> > > Appreciate if some one can help on it as it very important.
> > >
> > > Regards
> > > Sandip.
> > >
>

Thanks Bob,

The formula works fine now.....

Thanks a lot.

Regards
Sandip.


Bob Phillips wrote:
> =SUM(IF(FREQUENCY(IF((A2:A11<>"")*
> (B2:B11=2005)*(C2:C11="F"),
> MATCH(A2:A11,A2:A11,0)),ROW(INDIRECT("1:"&ROWS(A2:A11))))>0,1))
>
> --
>
> HTH
>
> Bob Phillips
>
> (replace xxxx in the email address with gmail if mailing direct)
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > How do I use the Count if or sumproduct formula to count cells with
> > unique items. The database is organised as follows
> >
> > Product Year Segment Customer Amt
> >
> > A 2005 F XA
> > A 2005 F XB
> > A 2005 F XA
> > A 2005 F XC
> > A 2005 G XA
> > A 2006 F
> > B 2005 F
> > B 2005 G
> > B 2006 G
> > B 2006 G
> >
> >
> > Now if I want to count the number of Unique customers in Product A, for
> > Year 2005, in Segment F, how is the countif or subproduct formula to be
> > set up.
> > By using the formula I should be able to get a count of 3 unique
> > customers and not 4 for the above criteria.
> >
> > Appreciate if some one can help on it as it very important.
> >
> > Regards
> > Sandip.
> >