Calculate the count without duplicates

[diego9010]

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.

[#2]

The desired formula should go for each row as shown in the table above on column "Resultad"

[#3]

@diego9010 

=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.

[#4]

@diego9010 

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.

[#5]

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!

[#6 · Accepted solution]

@diego9010 

=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.