how to count distinct values in excel for a matrix form

Question

I have looked if this has been asked, but could not find out exactly.

I' ve been trying to count distinct values. I tried sumproduct,sum(1/countif) etc, so far I got nothing but a div error or 0.

Basically, I' ve two columns: Campaign_no and customer_id. what I need is count unique customers for each campaigns and count unique customers that appears in the campaigns at the same time, sort of matrix.

The table is as follows:

Campaign_no	Cust_id
A	1
A	2
A	2
B	1
B	4
B	5
B	9
C	4
C	5
C	6
C	7

question from:https://stackoverflow.com/questions/65945913/how-to-count-distinct-values-in-excel-for-a-matrix-form

Answer 1

Here is a solution using helper cells.

C2 is =A2&B2. Copy it to C3:C12.

D2 is =IF(ISNA(MATCH(B2,D1:$D$1,0)),B2,""). Copy it to D3:D12.

E2 is =IF($D2="","",1-ISNA(MATCH(E$1&$D2,$C$2:$C$12,0))). Copy it to E2:G12.

E15 is =SUMIFS($E$2:$E$12,E2:E12,1). Copy it to F15:G15.

E16 is =SUMIFS($F$2:$F$12,E2:E12,1). Copy it to F16:G16.

E17 is =SUMIFS($G$2:$G$12,E2:E12,1). Copy it to F17:G17.

You may be able to get away without using the helper column C in Office 2017. I only have Office 365, so I couldn't it correctly.