Search code examples
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))
    • Using MAP() function to iterate through each cells in Company Column to get the running counts.
    • The above returns running counts thus equating to 1 which gives the unique brands but it does not takes in to account the Made in category, but if the data is consistent as shown in the OP then this should work.
    • The above returns TRUE and FALSE --> TRUE are those which are unique values and to get counts we are just summing them up.
    • Alternatively, if the column headers to be taken into account so as to make it dynamic then the following formula needs to be used:
    =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})))<>""))
    • Using XMATCH() get the matching header column and also verify the values to the respective columns are greater than 0
    • Wrapping within an MMULT() to return an output after an matrix product of two arrays.
    • Using FILTER() function grab only those greater than 0
    • Using UNIQUE() function to exclude duplicates
    • Finally, using SUM() we can also use ROWS() function or COUNTA() but avoiding to use, because the said function may return FALSE POSITIVES while using SUM() it wont and will return the exact output needed.