Forum Discussion
Solved
Calculate the count without duplicates
I am looking for a formula to calculate the count without duplicates for unique combinations of "Country," "Code," and "Serial" The result for country Mexico and Code 0014 should be 4 because it...
=MAP(A2:A10,B2:B10,LAMBDA(customer,code,COUNTA(UNIQUE(FILTER(C2:C10,(A2:A10=customer)*(B2:B10=code))))))
You are welcome. This formula is a little bit shorter.
Unfortunately i don't know how we can do this with SUMPRODUCT and COUNTIFS and without LAMBDA, UNIQUE functions.
=BYROW(A2:C10,LAMBDA(y,COUNTA(UNIQUE(TAKE(FILTER($A$2:$C$10,($A$2:$A$10=TAKE(y,,1))*($B$2:$B$10=CHOOSECOLS(y,2))),,-1)))))
With Office 365 or Excel 2021 or Excel for the web you can apply this formula.
Hi, thanks a lot.
Wow, that formula is very complex jeje. Would it be possible to do something with sumproduct and countifs for instance?
Thanks!
Wow, that formula is very complex jeje. Would it be possible to do something with sumproduct and countifs for instance?
Thanks!
=MAP(A2:A10,B2:B10,LAMBDA(customer,code,COUNTA(UNIQUE(FILTER(C2:C10,(A2:A10=customer)*(B2:B10=code))))))
You are welcome. This formula is a little bit shorter.
Unfortunately i don't know how we can do this with SUMPRODUCT and COUNTIFS and without LAMBDA, UNIQUE functions.
