Countif unique item with multiple criteria

[sanshah01]

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.

 

[Bob Phillips]

=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)

 

[Bob Phillips]

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)

 

[sanshah01]

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]

Sorry Sandip, mis-read it.

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


--

HTH

Bob Phillips

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

 

[sanshah01]

Thanks Bob,

The formula works fine now.....

Thanks a lot.

Regards
Sandip.