Forum Discussion
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 has 4 different Serial and for Canada's Code 0206 should be 3 (Three different Serial).
Can you please help with a formula to have this calculation?
| Customer Country Description | Code | Serial | Resultad |
| Mexico | 0014 | X | 4 |
| Mexico | 0014 | Y | 4 |
| Mexico | 0014 | Y | 4 |
| Mexico | 0014 | Z | 4 |
| Mexico | 0014 | Z | 4 |
| Mexico | 0014 | W | 4 |
| Canada | 0206 | A | 3 |
| Canada | 0206 | B | 3 |
| Canada | 0206 | C | 3 |
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.
=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!=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.
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
- The desired formula should go for each row as shown in the table above on column "Resultad"
