SOLVED

# Calculate the count without duplicates

Brass Contributor

# 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).

 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.

5 Replies

# Re: Calculate the count without duplicates

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

# Re: Calculate the count without duplicates

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

# Re: Calculate the count without duplicates

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.

# Re: Calculate the count without duplicates

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!
best response confirmed by diego9010 (Brass Contributor)
Solution

# Re: Calculate the count without duplicates

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

1 best response

Accepted Solutions
best response confirmed by diego9010 (Brass Contributor)
Solution

# Re: Calculate the count without duplicates

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