how to count without included duplicate records

[mountain]

I have the following data;

Country ProgID Partner ID
CN DPFR 2-2LI-48
CN DPFR 2-2LI-1
CN RPFR 2-2SJT5D
AU DPFR 2-BSZ-766
AU DPFR 2-BSZ-766
AU DPFR 2-BT2-9219
AU RPFR 2-BSZ-2009
AU RPFR 2-BSZ-2009
AU RPFR 2-BSZ-390
AU RPFR 2-BSZ-390
AU RPFR 2-BSZ-390

*table (A1:C12)
I want to count the total of partner ID (without count in the duplicate
records). The report should show something like the below
ProgID Country Total of Partner
DPFR CN
AU
RPFR CN
AU

I tried the frequency together with if. Somehow, I cant get it right..
Pls advise..

 

[Max]

One way is to try a pivot table (PT) ?
Here, think it'll deliver what you want, in a matter of seconds

Select any cell within the source table, click Data > Pivot table ...
Click Next > Next to proceed to step 3 of the wizard.

In step 3, click on "Layout"
Drag n drop ProgID within ROW area. Double-click on it, select "None" under
Subtotals.
Drag n drop Country within ROW area, below ProgID
Drag n drop Partner ID within DATA area. It'll appear as Count of Partner ID
Click OK > Finish.

Go to the PT sheet.
It'll look like this, which resembles exactly what you're after:

Count of Partner ID
ProgID Country Total
DPFR AU 3
CN 2
RPFR AU 5
CN 1
Grand Total 11

 

[mountain]

Thanks Max. I have tried to used the pivot table as well.

However, the result is not I want. This is because the result has included
the duplicate partner ID. What I need is that the the total count of Partners
should excluded the duplicate ID. The result should be like this...

Country ProgID Total Partner
DPFR CN 2
AU 2
RPFR CN 1
AU 2
Grand Total 7

 

[Max]

In your source table, add a label "Uniques" to D1, then put this in D2:
=IF(COUNTA(A2:C2)<3,"",IF(SUMPRODUCT((A$2:A2=A2)*(B$2:B2=B2)*(C$2:C2=C2))>1,"",1))
Copy D2 down to D12. Then just re-do the pivot, exchanging Count of Partner
ID with Sum of Uniques, and you'd get the required:

Sum of Unique
ProgID Country Total
DPFR AU 2
CN 2
RPFR AU 2
CN 1
Grand Total 7

 

[mountain]

That's great!!! Thanks Max!!

In your source table, add a label "Uniques" to D1, then put this in D2:
=IF(COUNTA(A2:C2)<3,"",IF(SUMPRODUCT((A$2:A2=A2)*(B$2:B2=B2)*(C$2:C2=C2))>1,"",1))
Copy D2 down to D12. Then just re-do the pivot, exchanging Count of Partner
ID with Sum of Uniques, and you'd get the required:

Sum of Unique
ProgID Country Total
DPFR AU 2
CN 2
RPFR AU 2
CN 1
Grand Total 7

 

[Max]

welcome, good to hear that.