Number of unique occurence

[Pierre]

Hello,

I would like to have formula which gives me the number of unique occurence within a range with criteria: in my example the number of unique parcel number receive per shop

Thanks in advance for helping

 

[pecoflyer]

Perhaps try a Pivot Table?

 

[bosco_yip]

In "Number of unique parcel" column, D2 copied down :

=SUMPRODUCT((A$2:A$11=A2)/COUNTIFS($A$2:$A$11,$A$2:$A$11,C$2:C$11,C$2:C$11))

Regards
Bosco

 

[Pierre]

Hello Bosco_yip

Thanks for the help, but when I try your formula it gives me a #value error Seems the problem comes with the "countifs"?

 

[AliGW]

Your formula is incorrect. See the amendment required in red below:

=SUMPRODUCT((A$2:A$11=A2)/COUNTIFS($A$2:$A$11,$A$2:$A$11,
C$2:C$11,C$2:C$11))

 

[Pierre]

Working

I don't know how I didn't see this... Thanks for the help!

 

[AliGW]

It was because of the point at which the text wrapped in the post. Had you copied and pasted, it would not have happened. Easily done, and glad to have helped.