SOLVED

Counting the number of values filtered by UNIQUE()

New Contributor

Hello, I have a table which I have filtered down by region (to only show Yorkshire depot box sales) which looks like so:

1453_walkers_co_uk_2-1659081054844.png

Which I have then filtered to remove the duplicate values with UNIQUE(A3:D20000)

1453_walkers_co_uk_4-1659081137142.png

I would like to get the number of values removed by UNIQUE(), not as in a grand total number of removed values, but rather how many duplicates of1453_walkers_co_uk_5-1659081255240.png

where removed, in other words, I'm trying to calculate the number of boxes sold to Yorkshire on the 11th week of 2016 using functions. I don't wish to use anything other than functions.

 

Any ideas? Thank you.

3 Replies
best response confirmed by 1453_walkers_co_uk (New Contributor)
Solution

@1453_walkers_co_uk 

After extracting unique values by function UNIQUE() use COUNTIFS() to count those unique values.

=COUNTIFS(A2:A14,F2:F4,B2:B14,G2:G4,C2:C14,H2:H4,D2:D14,I2:I4)

 

Harun24HR_0-1659083248388.png

 

Wow! You're incredible! I didn't know that syntax was possible! Thank you!

@1453_walkers_co_uk 

You are welcome. For my own interest I have build this formula to extract uniques and then count just by one formula.

 

=LET(x,A2:D14,y,BYROW(x,LAMBDA(a,CONCAT(a))),b,UNIQUE(y),c,BYROW(b,LAMBDA(t,COUNTA(FILTER(y,y=t)))),CHOOSE({1,2,3,4,5},INDEX(UNIQUE(x),,1),INDEX(UNIQUE(x),,2),INDEX(UNIQUE(x),,3),INDEX(UNIQUE(x),,4),c))

 

Download the attach file.