excelexcel-formulacountsumcountif

How to count all the sub categories but only count them once if the main categories are the same


Company Brands Made in CHI Made in TW Made in USA
AA Apple 6 0 1
BB BoyO 0 1 0
BB BoyX 1 0 0
CC Car 17 1 0
DD DogO 4 0 1
DD DogX 1 0 1
EE Elva 8 1 0

Question 1

I would like to check how many companies have products Made in CHI or Made in USA (Some company have 2 or more brands but i just want to count 1 time only, like DogO and DogX both brands have products made in CHI or USA, but they belong to the same company, so I will only count 1 time for that)

And The result should be 5 (which are Company AA, BB, CC, DD, EE)


Question 2

I would also like to check the number of companies have product made in USA (Also DogO and DogX has product made in USA but they are from same company so I just want to count 1 only)

The result should be 2 (which are Company AA & DD)


Solution

  • You could try the following formulas:

    • For Question One:

    enter image description here


    =SUM(N(MAP(A2:A8,LAMBDA(x, COUNTIF(A2:x,x)))=1))
    


    =SUM(N(FILTER(A2:A8,MMULT( (1-ISNA(XMATCH(C1:E1,{"Made in CHI","Made in USA"})))* (MAP(A2:A8,LAMBDA(x,
       COUNTIF(A2:x,x)))=1),{1;1;1}))<>""))
    

    Or a bit shorter alternative, may be :

    =ROWS(UNIQUE(TOCOL(IFS(FILTER(C2:E8,1-ISNA(XMATCH(C1:E1,{"Made in CHI","Made in USA"})))>0,A2:A8),2)))
    

    Or:

    =ROWS(UNIQUE(TOCOL(IFS(1-ISNA(XMATCH(C1:E1,{"Made in CHI","Made in USA"})),A2:A8),2)))
    

    • For Question Two:

    =SUM(N(UNIQUE(FILTER(A2:A8,MMULT((1-ISNA(XMATCH(C1:E1,"Made in USA")))*(C2:E8>0),{1;1;1})))<>""))