Forum Discussion

Brass Contributor

Aug 08, 2023

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

excel

Formulas and Functions

OliverScheurich
Aug 08, 2023
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.

OliverScheurich

Gold Contributor

Aug 08, 2023

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.

diego9010

Brass Contributor

Aug 08, 2023

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!

OliverScheurich
Gold Contributor
Aug 08, 2023
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.