Distinct count using countifs in excel-CodePudding

I have a following excel data format:

ID       Category      Type
P-1      A1            Pro
P-2      A2            Demo
P-3      A2            
P-4      A1            Pro
P-4      A1            Pro
P-5      B1            Pro
P-6      B3            Demo

Using the above excel I need to create the following format and get count.

Category    Pro    Demo    Blank
A1          2      1       0
A2          0      0       1
B1          1      0       0
B3          0      1       0

I'm using =SUMPRODUCT(1/COUNTIFS()) but it is not working as expected.

CodePudding user response：

Formula I used-

E2 =UNIQUE(B2:B8)
F2 =SUM(--(UNIQUE(FILTER($A$2:$A$8,($B$2:$B$8=$E2)*($C$2:$C$8=F$1),""))<>""))
G2 =SUM(--(UNIQUE(FILTER($A$2:$A$8,($B$2:$B$8=$E2)*($C$2:$C$8=G$1),""))<>""))
H2 =SUM(--(UNIQUE(FILTER($A$2:$A$8,($B$2:$B$8=$E2)*($C$2:$C$8=""),""))<>""))

CodePudding user response：

You can use countifs

E2    =UNIQUE(B2:B8)
F2    =COUNTIFS($B$2:$B$8,$E2,$C$2:$C$8,F$1)  
G2    =COUNTIFS($B$2:$B$8,$E2,$C$2:$C$8,G$1)
H2    =SUM(COUNTIF($B$2:$B$8,$E2)-$F2-$G2)


A        B             C        D      E             F        G         H
ID       Category      Type            Category      Pro      Demo      Blank
P-1      A1            Pro             A1            3        0         0
P-2      A2            Demo            A2            0        1         1
P-3      A2                            A3            1        0         0
P-4      A1            Pro             A4            0        1         0     
P-4      A1            Pro
P-5      B1            Pro
P-6      B3            Demo